STR_TO_DATE

Syntax

STR_TO_DATE(str, format)

Purpose

You can call this function to convert str into a DATETIME, DATE, or TIME value based on format. The return value type depends on the parts of the date and time that are included in format.

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

• If str is not a valid date, the return value varies with the settings of sql_mode:

  • If sql_mode contains NO_ZERO_IN_DATE, NULL is returned.

  • If sql_mode does not contain NO_ZERO_IN_DATE:

    • If str is an integer, and str < 0, it returns 0000-00-00 00:00:00 and displays a warning prompt.

      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 (0.00 sec)
      

    • If str is an integer and 0 ≤ str ≤ 69, it returns the year of the sum of 1999-11-30 00:00:00 plus str.

      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 (0.00 sec)
      

    • If str is an integer and 69 < str ≤ 99, it returns the year of the result of 1969-11-30 00:00:00 plus str minus 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 set (0.00 sec)
      

    • If str is an integer and 100 < str ≤ 9999, it returns the year of the result of 0099-11-30 00:00:00 plus str minus 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 set (0.00 sec)
      

    • If str is an integer, and str > 10000, it returns NULL and displays a warning prompt.

      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 (0.00 sec)
      

    • If str is a string, the function converts the string to a datetime if possible, and returns the result of the datetime value of str plus 1999-11-30 00:00:00. If str cannot be converted to a datetime, NULL is 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 (0.00 sec)
      
      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 (0.01 sec)
      

    • If str is neither an integer or string, it returns NULL and displays a warning prompt.

      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 (0.00 sec)
      
      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 (0.01 sec)
      

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 (0.01 sec)