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

Converting String to Date for MySQL Insertion/Update

When inserting data into MySQL's TIMESTAMP or DATE fields, ensuring the correct date format is crucial. As DATE_FORMAT() only formats existing dates, how do we convert a string representation like '15-Dec-09' into a date?

Solution: STR_TO_DATE() Function

MySQL provides the STR_TO_DATE() function, which is the inverse of DATE_FORMAT(). It converts a string into a date using a specified format string. The syntax is:

STR_TO_DATE(str,format)

Usage:

To convert '15-Dec-09' into a date with the format 'dd-mmm-yy', use:

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

Result:

+------------+
| date       |
+------------+
| 2009-12-15 |
+------------+

The STR_TO_DATE() function successfully converts the string to a date, allowing for its insertion or update into a TIMESTAMP or DATE field in MySQL.

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