What is the correct DateTime format for MySQL Database?

Question

I am Inserting this DateTime data '12/21/2012 1:13:58 PM' into my MySQL Database using this SQL string:

String Query = "INSERT INTO `restaurantdb`.`stocksdb` 
                  (`stock_ID`,`stock_dateUpdated`) 
                  VALUES ('@stockID' '@dateUpdated');

and I receive this error message:

Incorrect datetime value: '12/21/2012 1:13:58 PM' for column 'stock_dateUpdated' at row 1

So what is the right format/value for dateTime to input into a MySQL database?

Answer 1

Q: What is the right format/value for DATETIME literal within a MySQL statement?

A: In MySQL, the standard format for a DATETIME literal is:

 'YYYY-MM-DD HH:MI:SS'

with the time component as a 24 hour clock (i.e., the hours digits provided as a value between 00 and 23).

MySQL provides a builtin function STR_TO_DATE which can convert strings in various formats to DATE or DATETIME datatypes.

So, as an alternative, you can also specify the value of a DATETIME with a call to that function, like this:

STR_TO_DATE('12/21/2012 1:13:58 PM','%m/%d/%Y %h:%i:%s %p')

So, you could have MySQL do the conversion for you in the INSERT statement, if your VALUES list looked like this:

... VALUES ('@stockID', STR_TO_DATE('@dateUpdated','%m/%d/%Y %h:%i:%s %p');

(I notice you have a required comma missing between the two literals in your VALUES list.)

---

MySQL does allow some latitude in the delimiters between the parts of the DATETIME literal, so they are not strictly required.

MySQL 5.5 Reference Manual.