mcgowan.b mcgowan.b - 5 months ago 19
MySQL Question

Create datetime with specific time mySql

I'm looking to create a date time field in a mySql script thhat has a specific date and time.

I've tried using

CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 9 DAY) ,'%Y%m%d'), ' 13:00:00')

but it doesn't insert correctly.

How can I achieve this so that it will insert a date time with the time as above?

It inserts the record as
0000-00-00 00:00:00
with the above

Answer

mysql accepts datetime values in yyyy-mm-dd hh:mm:ss format. In your formula you do not separate the year, month, day values, hance the result is not in the date format mysql expects the dates in. Change it to:

CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 9 DAY) ,'%Y-%m-%d'), ' 13:00:00')

But I do not really understand why you need to do the formatting, just use CURDATE() function instead of the NOW():

CONCAT(DATE_SUB(CURDATE(), INTERVAL 9 DAY), ' 13:00:00')