CODEWITHSUNDEEP
mysqldatetime
gregor
gregor

Reputation: 4833

Creating DATETIME from DATE and TIME

Is there way in MySQL to create DATETIME from a given attribute of type DATE and a given attribute of type TIME?

Upvotes: 64

Views: 72009

Answers (6)

Christian
Christian

Reputation: 1460

select timestamp('2003-12-31 12:00:00','12:00:00');

works, when the string is formatted correctly. Otherwise, you can just include the time using str_to_date. 

select str_to_date('12/31/2003 14:59','%m/%d/%Y %H:%i');

Upvotes: 1

johnson
johnson

Reputation: 4415

Copied from the MySQL Documentation:

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

mysql> SELECT TIMESTAMP('2003-12-31');
    -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
    -> '2004-01-01 00:00:00'

Upvotes: 70

SystemParadox
SystemParadox

Reputation: 8647

You could use ADDTIME():

ADDTIME(CONVERT(date, DATETIME), time)
  • date may be a date string or a DATE object.
  • time may be a time string or a TIME object.

Tested in MySQL 5.5.

Upvotes: 31

Michael Grazebrook
Michael Grazebrook

Reputation: 5671

Without creating and parsing strings, just add an interval to the date:

set @dt_text = '1964-05-13 15:34:05.757' ;
set @d = date(@dt_text) ;
set @t = time(@dt_text) ;
select @d, @t, @d + interval time_to_sec( @t ) second;

However this truncates the microseconds.

I agree with Muki - be sure to take account of time zones and daylight savings time!

Upvotes: 0

CDuv
CDuv

Reputation: 2260

To get a true DATETIME value from your two separate DATE and TIME values:

STR_TO_DATE(CONCAT(date, ' ', time), '%Y-%m-%d %H:%i:%s')

Upvotes: 47

Zed
Zed

Reputation: 57658

datetime = CONCAT(date, ' ', time);

Upvotes: 9

Related Questions