How Can MySQL\'s STR_TO_DATE() Function Convert Strings to Dates?

Converting Strings to Dates in MySQL with STR_TO_DATE()

When working with MySQL databases, there may be instances where you need to convert strings representing dates into appropriate formats for insertion or update in TIMESTAMP or DATE fields. While the DATE_FORMAT() function is commonly used for formatting dates, it doesn't offer the ability to convert strings back into dates.

To address this need, MySQL provides the STR_TO_DATE() function. This function performs the inverse operation of DATE_FORMAT(), allowing you to convert strings into DATETIME, DATE, or TIME values.

STR_TO_DATE() Syntax

The syntax for STR_TO_DATE() is as follows:

STR_TO_DATE(str, format)

Where:

• str: The string to be converted into a date value.
• format: The format string that defines the format of the input string.

Example

Let's consider the task of converting the string '15-Dec-09' into a DATE value for insertion into a database table. To do this, we can use the following query:

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

In this example, the %d-%b-%y format specifiers indicate that the day, month, and year should be extracted and converted into a DATE value.

Output

The query will return the following result:

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

This shows that the string '15-Dec-09' has been successfully converted into a DATE value of '2009-12-15'.

The above is the detailed content of How Can MySQL\'s STR_TO_DATE() Function Convert Strings to Dates?. For more information, please follow other related articles on the PHP Chinese website!