top of page




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 the date,
time, or datetime value extracted from str is illegal, STR_TO_DATE()
returns NULL and produces a warning.

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'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
-> '2013-05-01'

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'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
-> '09:30:17'

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

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



bottom of page