TO_DATE Function

Syntax

TO_DATE (value_string, format_specifier)

Description

The TO_DATE function converts a string containing date or date and time value to a DATE datatype. In database compatibility mode, the function returns a DATE type2 object, else returns a DATE object.

Parameters

value_string
is the date or date and time value that will be converted to a DATE datatype.
format_specifier
is the format used to interpret the value_string. The default format is YYYY-MM-DD for SQL/MX Date and YYYY-MM-DD HH24:MI:SS for DATE type2. The format_specifier is an optional parameter. If it is not specified, then the default format is used. To view the format_specifier elements that are supported in SQL/MX, see Format string elements.
The parameters can be any of the following types:
  • String-literal

  • Host-variable

  • Column reference

  • Dynamic parameter

  • Output of a function

Considerations

  • A missing day in date field defaults to first day of specified month.

  • A missing month in date field defaults to current month.

  • A missing year in date field defaults to current year.

  • The function ignores extra leading and trailing spaces in the input arguments.

  • The function ignores extra separators in the input arguments.

  • Value_string or format_specifier can have different separators.

  • Format_specifier elements are case insensitive.

  • Only ISO88591 character set is supported for input strings. Only CHAR and VARCHAR datatypes are supported.

  • Time zones, NULL value, and SQL/MP tables are not supported.

  • Use of TO_DATE function in MV definition is not supported in database compatibility mode.

Examples

  • In the following example, the date value is returned, even though the separators used in the value string and format string are different.
    >>select TO_DATE ('31-DEC-2015','DD-MON-YYYY') from (values(1))t;
    
    (EXPR)
    ----------
    
    2015-12-31
    
    --- 1 row(s) selected.
    
  • In the following example, the default format is used as the format_specifier is not specified.

    >>select TO_DATE ('2016-08-16') from (values(1))t;
    
    (EXPR)
    ----------
    
    2016-08-16
    
    --- 1 row(s) selected.
    
  • In the following example, an error message is returned because the hour is missing.
    >>select TO_DATE ('29-FEB-2016', 'DD-MON-YYYY HH' ) from (values(1))t;
    
    *** ERROR[8973] Value string '29-FEB-2016' doesnot match with format 'DD-MON-YYYY HH'
    
    --- 0 row(s) selected.
  • In the following example, an error message is displayed as the value string is an invalid date.
    >>select TO_DATE ('32-DEC-2015','DD-MON-YYYY') from (values(1))t;
    
    *** ERROR[8972] Invalid value 32 for DAY.
    
    --- 0 row(s) selected.