Take the 2-minute tour ×

If I have a string that represents the date and time such as '30/09/2011 10:14' which is a non-standard DATETIME format. Can I reformat this to a standard DATETIME format on insert into a DATETIME field in a MySQL database table?

I have tried this:

INSERT INTO test(lastdate) VALUES(DATE_FORMAT('30/09/2011 10:14', '%d/%m/%Y %H:%i'))

The result is NULL in the lastdate DATETIME field.

share|improve this question
you don't INSERT using date_format, you SELECT using a date_format –  Book Of Zeus Nov 7 '11 at 11:44
I understand that but how can I tell MySQL to insert a datetime in this format? –  aHunter Nov 7 '11 at 11:49
Have a look at STR_TO_DATE function. –  Devart Nov 7 '11 at 11:50

2 Answers 2

up vote 1 down vote accepted

You can use str_to_date to convert for your insert :

INSERT INTO test(lastdate) values (str_to_date('30/09/2011 10:14',"%d/%m/%Y %h:%i"))

DATE_FORMAT is for translating a date / time field to another format on select

share|improve this answer

Change date_format to str_to_date and you'll be good to go.

share|improve this answer

Your Answer


