How Can I Parse Date Strings into MySQL DATE or DATETIME Values?

Parsing Dates for MySQL

MySQL provides robust functions for manipulating date and time values. One common task is converting string representations of dates into a format suitable for insertion or update into TIMESTAMP or DATE fields.

STR_TO_DATE(): The Inverse of DATE_FORMAT()

While the DATE_FORMAT() function can format dates, its inverse is the STR_TO_DATE() function.

Syntax:

STR_TO_DATE(str, format)

Parameters:

• str: The string representation of the date.
• format: A string specifying the format of the input date.

Usage:

STR_TO_DATE() parses the input string using the specified format and returns a DATETIME, DATE, or TIME value accordingly. If the extracted value is invalid, it returns NULL and generates a warning.

Example: Parse Date Constant

Consider the string representation of a date:

'15-Dec-09'

To convert this string into a DATE value, we can use the following query:

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

This query parses the string using the format '%d-%b-%y', where '%d' represents the day of the month, '%b' represents the abbreviated month name, and '%y' represents the year with two digits.

Output:

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

Now, the parsed DATE value can be used for insertion or update operations in MySQL TIMESTAMP or DATE fields.

The above is the detailed content of How Can I Parse Date Strings into MySQL DATE or DATETIME Values?. For more information, please follow other related articles on the PHP Chinese website!