MySQL STR_TO_DATE() Function

Summary: in this tutorial, we will show you how to use the MySQL STR_TO_DATE() function to convert a string into a date, time, or datetime value.

Introduction to MySQL STR_TO_DATE function

The STR_TO_DATE() converts a string into a date value based on a specified format string.

Here’s the syntax of the STR_TO_DATE() function:

STR_TO_DATE(str,fmt);Code language: SQL (Structured Query Language) (sql)

In this syntax:

str: This is the input string that you want to convert.
fmt: This is the format string that includes format specifiers. For example, %d for day, %m for month and %y for year.

The STR_TO_DATE() function may return a DATE , TIME, or DATETIME value based on the input and format strings.

If the input string is illegal, the STR_TO_DATE() function returns NULL.

The STR_TO_DATE() function scans the input string to match the format string. The format string may contain literal characters and format specifiers that begin with a percentage (%) character.

Refer to the DATE_FORMAT function for the list of format specifiers.

The STR_TO_DATE() function is very useful in data migration that involves temporal data conversion from an external format to MySQL temporal data format.

MySQL STR_TO_DATE examples

Let’s look at some examples of using STR_TO_DATE() function to convert strings into date and/or time values

The following statement converts a string into a DATE value.

SELECT STR_TO_DATE('21,5,2013','%d,%m,%Y');Code language: SQL (Structured Query Language) (sql)

Try It Out

Output:

+-------------------------------------+
| STR_TO_DATE('21,5,2013','%d,%m,%Y') |
+-------------------------------------+
| 2013-05-21                          |
+-------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Based on the format string ‘%d, %m, %Y’, the STR_TO_DATE() function scans the ‘21,5,2013’ input string.

First, it attempts to find a match for the %d format specifier, which is a day of the month (01…31), in the input string. Because the number 21 matches with the %d specifier, the function takes 21 as the day value.
Second, because the comma (,) literal character in the format string matches with the comma in the input string, the function continues to check the second format specifier %m , which is a month (01…12), and finds that the number 5 matches with the %m format specifier. It takes the number 5 as the month value.
Third, after matching the second comma (,), the STR_TO_DATE() function keeps finding a match for the third format specifier %Y , which is a four-digit year e.g., 2012,2013, etc., and it takes the number 2013 as the year value.

The STR_TO_DATE() function ignores extra characters at the end of the input string when it parses the input string based on the format string. See the following example:

SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');Code language: SQL (Structured Query Language) (sql)

Try It Out

Output:

+------------------------------------------------------+
| STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y') |
+------------------------------------------------------+
| 2013-05-21                                           |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)Code language: JavaScript (javascript)

The STR_TO_DATE() sets all incomplete date values, which are not provided by the input string, to NULL. See the following example:

SELECT STR_TO_DATE('2013','%Y');Code language: SQL (Structured Query Language) (sql)

Try It Out

+--------------------------+
| STR_TO_DATE('2013','%Y') |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set, 1 warning (0.00 sec)Code language: JavaScript (javascript)

Because the input string only provides a year value, the STR_TO_DATE() function returns a date value that has month and day set to NULL. Notice that in MySQL 5.7 or earlier, the STR_TO_DATE() sets month and day to zero.

The following example converts a time string into a TIME value:

SELECT STR_TO_DATE('113005','%h%i%s');Code language: SQL (Structured Query Language) (sql)

Try It Out

+--------------------------------+
| STR_TO_DATE('113005','%h%i%s') |
+--------------------------------+
| 11:30:05                       |
+--------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Similar to the unspecified date part, the STR_TO_DATE() function sets unspecified time part to zero, see the following example:

SELECT STR_TO_DATE('11','%h');Code language: SQL (Structured Query Language) (sql)

Try It Out

+------------------------+
| STR_TO_DATE('11','%h') |
+------------------------+
| 11:00:00               |
+------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example converts the string into a DATETIME value because the input string provides both date and time parts.

SELECT STR_TO_DATE('20130101 1130','%Y%m%d %h%i') ;Code language: SQL (Structured Query Language) (sql)

Try It Out

+--------------------------------------------+
| STR_TO_DATE('20130101 1130','%Y%m%d %h%i') |
+--------------------------------------------+
| 2013-01-01 11:30:00                        |
+--------------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use the MySQL STR_TO_DATE() function to convert strings to date and time values.

Was this tutorial helpful?