How to Convert String Dates to MySQL DATE or TIMESTAMP Values?

Parsing Dates in MySQL

Question:

How can we convert a string representation of a date, such as '15-Dec-09', into a valid DATE or TIMESTAMP field for insertion or updating in a MySQL database?

Answer:

To parse a date string into a MySQL date or timestamp value, we need to utilize the STR_TO_DATE() function, which is the inverse of the DATE_FORMAT() function.

STR_TO_DATE() Function:

The STR_TO_DATE() function takes two arguments:

• str: The string containing the date or time information to be parsed.
• format: A format string that specifies the date or time format of the input string.

Example:

To convert the string '15-Dec-09' into a DATE field, we can use the following query:

SELECT STR_TO_DATE('15-Dec-09', '%d-%b-%y') AS date;

The format string '%d-%b-%y' indicates that the input string is in the format of day-month-year, with two digits for the year.

Output:

+------------+
| date       |
+------------+
| 2009-12-15 |
+------------+
1 row in set (0.00 sec)

The above is the detailed content of How to Convert String Dates to MySQL DATE or TIMESTAMP Values?. For more information, please follow other related articles on the PHP Chinese website!