11

I am trying to get a DATETIME field from a DATE and a TIME field. none of the functions in MYSQL seems useful.

Is somebody aware how to do this or that if this can even be done? :)

GMB
  • 216,147
  • 25
  • 84
  • 135
Anup
  • 113
  • 1
  • 1
  • 5

4 Answers4

14

It should be as easy as

UPDATE table SET datetime_field = CONCAT(date_field, " ", time_field);
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
9

Both of the other answers do not convert the date properly if use use a TIME of "838:00:00" which is a valid time according to the mysql manual

so instead you can try converting the time field to seconds and then adding them
for example:

date_field + INTERVAL TIME_TO_SEC(time_field) SECOND

This will convert the date accordingly

Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143
4
addtime(date_field, time_field)
Matt Sinclair
  • 1,112
  • 9
  • 10
  • please consider the following [How do I write a good answer?](http://stackoverflow.com/help/how-to-answer) – hassan Apr 27 '17 at 13:30
  • also , are you sure that `addtime` is mysql defined function ? – hassan Apr 27 '17 at 13:32
  • Hey Hassan - I don't see why it's necessary to add anything more to my answer - people want a quick solution. Here's the mysql function documented: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html – Matt Sinclair Aug 30 '17 at 13:40
  • if date_field is DATE type you'll need to cast it to DATETIME first as it won't do automatic type promotion from DATE to DATETIME – AndrewWhalan May 16 '20 at 12:16
1

@Pekka is right.

Also you can use CONCAT_WS(seperator, val1, val2,....)

CONCAT_WS(' ', date_field,time_field)
Sadat
  • 3,493
  • 2
  • 30
  • 45