How to convert a string to date in MySQL?

In MySQL, converting strings to date types typically uses the STR_TO_DATE() function. This function is highly effective as it can convert text-based date strings into datetime types based on a specified format string.

Using the STR_TO_DATE() Function

The basic syntax is as follows:

sql
STR_TO_DATE(your_string, format_mask)

• your_string is the string to be converted.
• format_mask specifies the format of the input string.

Example

Suppose we have a string containing date information, '2021-08-15', with format '%Y-%m-%d', and we want to convert it to a date type. We can use the following SQL command:

sql
SELECT STR_TO_DATE('2021-08-15', '%Y-%m-%d');

This will return a date value of 2021-08-15.

More Complex Example

If the string format includes time and is in 12-hour format with AM/PM, for example, 'August 15, 2021 10:15:30 PM', the format mask will differ:

sql
SELECT STR_TO_DATE('August 15, 2021 10:15:30 PM', '%M %d, %Y %h:%i:%s %p');

Here, %M represents the full month name, %d is the two-digit day, %Y is the four-digit year, %h is the hour (12-hour format), %i is minutes, %s is seconds, and %p represents AM or PM.

Important Notes

• Ensure that format_mask exactly matches the format of your input string; otherwise, the conversion will fail.
• If the string format is incorrect or does not match the specified format_mask, STR_TO_DATE() will return NULL.

Using the STR_TO_DATE() function effectively helps you handle and convert date strings in SQL queries, making data analysis and operations more flexible and powerful.