STR_TO_DATE(str,format)

MySQL 8.4

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If str or format is NULL, the function returns NULL. If the date, time, or datetime value extracted from str cannot be parsed according to the rules followed by the server, STR_TO_DATE() returns NULL and produces a warning.

Syntax

MYSQL

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
'2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
'2013-05-01'

Examples

Example

The server scans str attempting to match format to it. The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. For the specifiers that can be used in format, see the DATE_FORMAT() function description.

MYSQL

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
'2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
'2013-05-01'

Example 2

Scanning starts at the beginning of str and fails if format is found not to match. Extra characters at the end of str are ignored.

MYSQL

SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
'09:30:17'
SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
NULL
SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
'09:30:17'

Example 3

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

MYSQL

SELECT STR_TO_DATE('abc','abc');
'0000-00-00'
SELECT STR_TO_DATE('9','%m');
'0000-09-00'
SELECT STR_TO_DATE('9','%s');
'00:00:09'

Example 4

Range checking on the parts of date values is as described in Section 13.2.2, “The DATE, DATETIME, and TIMESTAMP Types”. This means, for example, that “zero” dates or dates with part values of 0 are permitted unless the SQL mode is set to disallow such values.

MYSQL

SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
'0000-00-00'
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
'2004-04-31'

Example 5

If the NO_ZERO_DATE SQL mode is enabled, zero dates are disallowed. In that case, STR_TO_DATE() returns NULL and generates a warning:

MYSQL

SET sql_mode = '';
SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00                            |
+---------------------------------------+
SET sql_mode = 'NO_ZERO_DATE';
SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '00/00/0000' for function str_to_date

Example 6

In some previous versions of MySQL, it was possible to pass an invalid date string such as '2021-11-31' to this function. In MySQL 8.4, STR_TO_DATE() performs complete range checking and raises an error if the date after conversion would be invalid.

MYSQL

SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
'2004-10-18'