STR_TO_DATE

Syntax

STR_TO_DATE(str, format)

Purpose

Converts the str value to a DATETIME, DATE, or TIME value by using the format parameter. The return value type depends on the parts of the date and time in the format parameter.

• If str or format is NULL, the function returns NULL.

• If str is not in a valid date format, the return value depends on the setting of sql_mode:

  • If sql_mode contains NO_ZERO_IN_DATE, the function returns NULL.

  • If sql_mode does not contain NO_ZERO_IN_DATE:

    • If str is an integer and str is less than 0, the function returns 0000-00-00 00:00:00 and issues 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 warning
      

    • If str is an integer and 0 ≤ str ≤ 69, the function returns 1999-11-30 00:00:00 plus the year specified by str and issues a warning.

      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 set
      

    • If str is an integer and 69 < str ≤ 99, the function returns 1969-11-30 00:00:00 plus the year specified by str - 70 and issues a warning.

      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 set
      

    • If str is an integer and 100 < str ≤ 9999, the function returns 0099-11-30 00:00:00 plus the year specified by str - 100 and issues a warning.

      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 set
      

    • If str is an integer and str is greater than 10000, the function returns NULL and issues 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 warning
      

    • If str is a string, the function attempts to convert the string to a date and time value and returns 1999-11-30 00:00:00 plus the date and time specified by str. If the string cannot be converted to a date and time value, the function returns NULL.

      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 set
      

    • If str is of another type, the function returns NULL and issues 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 information about the supported date and time formats, 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