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’); -> NULL 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 0: 

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’ 

Range checking on the parts of date values is as described in Section 11.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’ mysql> SELECT STR_TO_DATE(’04/31/2004’, ’%m/%d/%Y’); -> ’2004-04-31’ 

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 = ’’; mysql> SELECT STR_TO_DATE(’00/00/0000’, ’%m/%d/%Y’); +---------------------------------------+ | STR_TO_DATE(’00/00/0000’, ’%m/%d/%Y’) | +---------------------------------------+ | 0000-00-00 | +---------------------------------------+ mysql> SET sql_mode = ’NO_ZERO_DATE’; mysql> SELECT STR_TO_DATE(’00/00/0000’, ’%m/%d/%Y’); +---------------------------------------+ | STR_TO_DATE(’00/00/0000’, ’%m/%d/%Y’) | +---------------------------------------+ | NULL | +---------------------------------------+ 

