Declaration
STR_TO_DATE(str, format)
Description
Use format to convert str to a DATETIME, DATE, or TIME value. The return type depends on which date and time parts format includes.
If
strorformatisNULL, the function returnsNULL.If
stris not in a valid date format, the return value varies depending on thesql_modesetting:sql_modeincludesNO_ZERO_IN_DATE: ReturnsNULL.sql_modedoes not containNO_ZERO_IN_DATE:If
stris of the INT type andstr< 0, return0000-00-00 00:00:00and issue a warning.obclient> SELECT STR_TO_DATE(-100,'%Y-%b-%D %r'); +---------------------------------+ | STR_TO_DATE(-100,'%Y-%b-%D %r') | +---------------------------------+ | 0000-00-00 00:00:00 | +---------------------------------+ 1 row in set, 1 warningIf
stris of the INT type and 0 ≤str≤ 69, then return1999-11-30 00:00:00plus the year ofstr;obclient> SELECT STR_TO_DATE(0,'%Y-%b-%D %r'); +------------------------------+ | STR_TO_DATE(0,'%Y-%b-%D %r') | +------------------------------+ | 1999-11-30 00:00:00 | +------------------------------+ 1 row in setIf
stris of the INT type and 69 <str≤ 99, return1969-11-30 00:00:00plus the year ofstrminus 70.obclient> SELECT STR_TO_DATE(88,'%Y-%b-%D %r'); +-------------------------------+ | STR_TO_DATE(88,'%Y-%b-%D %r') | +-------------------------------+ | 1987-11-30 00:00:00 | +-------------------------------+ 1 row in setIf
stris of the INT type and 100 <str≤ 9999, return0099-11-30 00:00:00plus the year ofstrminus 100.obclient> SELECT STR_TO_DATE(2088,'%Y-%b-%D %r'); +---------------------------------+ | STR_TO_DATE(2088,'%Y-%b-%D %r') | +---------------------------------+ | 2087-11-30 00:00:00 | +---------------------------------+ 1 row in setIf
stris of the INT type andstr> 10,000, returnNULLand issue a warning.obclient> SELECT STR_TO_DATE(10001,'%Y-%b-%D %r'); +----------------------------------+ | STR_TO_DATE(10001,'%Y-%b-%D %r') | +----------------------------------+ | NULL | +----------------------------------+ 1 row in set, 1 warningIf
stris a string, it will be converted to a date and time as much as possible. The result is a date and time of the type1999-11-30 00:00:00plusstr. If conversion to a date and time is not possible,NULLis returned.obclient> SELECT STR_TO_DATE('a2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r'); +------------------------------------------------------+ | STR_TO_DATE('a2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r') | +------------------------------------------------------+ | NULL | +------------------------------------------------------+ 1 row in set, 1 warning obclient> SELECT STR_TO_DATE('a2014-Jan-1st 5:5:5 pm', 'a%Y-%b-%D %r'); +-------------------------------------------------------+ | STR_TO_DATE('a2014-Jan-1st 5:5:5 pm', 'a%Y-%b-%D %r') | +-------------------------------------------------------+ | 2014-01-01 05:05:05 | +-------------------------------------------------------+ 1 row in setIf
stris of another type, returnNULLand issue a warning.obclient> SELECT STR_TO_DATE(1.6,'%Y-%b-%D %r'); +--------------------------------+ | STR_TO_DATE(1.6,'%Y-%b-%D %r') | +--------------------------------+ | NULL | +--------------------------------+ 1 row in set, 1 warning obclient> SELECT STR_TO_DATE(pi(),'%Y-%b-%D %r'); +---------------------------------+ | STR_TO_DATE(pi(),'%Y-%b-%D %r') | +---------------------------------+ | NULL | +---------------------------------+ 1 row in set, 1 warning
For more information about the formats supported by FORMAT, see DATE_FORMAT.
Examples
obclient> SELECT STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r');
+-----------------------------------------------------+
| STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r') |
+-----------------------------------------------------+
| 2014-01-01 05:05:05 |
+-----------------------------------------------------+
1 row in set