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. Theformat_specifier
is an optional parameter. If it is not specified, then the default format is used. To view theformat_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
orformat_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.