COnverting SQL Date into SQL DateTime

Hi,
I have a mistake while creating the database field earlier.
I have DATE and TIME fields in my MYSQL Database. Now I want to convert it into DATETIME format, because I need to do querying the data based from the date and specific hours.

I dont know how to join between DATE & TIME field into new field with DATETIME format,
if i do joining in Ms Excel with concatenate function, will it work ?

thanks
regards,
arief

If you want to add a new field/column to your MySQL database with the Date and Time, if you have:

  • datetimetest table
  • date field
  • time field
  • want to add dateandtime field
    you can execute this SQL:

ALTER TABLE datetimetest ADD COLUMN dateandtime DATETIME; UPDATE datetimetest set dateandtime = CONCAT(`date`, ' ', `time`);

Or you could try the TIMESTAMP(date, time) function to join both fields/columns on the fly.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestamp

Just learn about TIMESTAP if you haven’t used it before:

[quote]The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time.[/quote]

TIMESTAMP datatype has this small range limitation, BUT, the TIMESTAMP() function does not.
How do I know? I tested it. Check: SELECT TIMESTAMP(‘2099-01-01’), it works.

So, an: UPDATE datetimetest set dateandtime = TIMESTAMP(`date`, `time`);

Should be ok too.

[code]SELECT TIMESTAMP(‘2099-01-01’, ‘12:32:00’)

Result: ‘2099-01-01T12:32:00Z’[/code]

thanks, its works now.

regards,
Arief