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?