Casting Numeric Date and Time Strings to DATE Data Types

The database server provides a built-in cast to convert DATETIME values to DATE values, as in the following SPL program fragment:

DEFINE my_date DATE;
DEFINE my_dt DATETIME YEAR TO SECOND;
. . .
LET my_date = CURRENT;

Here the DATETIME value that CURRENT returns is implicitly cast to DATE. You can also cast DATETIME to DATE explicitly:

LET my_date = CURRENT::DATE;

Similarly, you can cast explicitly a string that has the format of the Numeric Date and Time segment, as defined in the Literal DATETIME syntax diagram, to a DATETIME data type, as in the following example:

LET my_dt = 
   ('2008-02-22 05:58:44.000')::DATETIME YEAR TO SECOND;

There is neither an implicit nor an explicit built-in cast, however, for directly converting a character string that has the Numeric Date and Time format to a DATE value. Both of the following statements, for example, fail with error -1218:

LET my_date = ('2008-02-22 05:58:44.000');
LET my_date = ('2008-02-22 05:58:44.000')::DATE;

To convert a character string that specifies a valid numeric date and time value to a DATE data type, you must first cast the string to DATETIME, and then cast the resulting DATETIME value to DATE, as in this example:

LET my_date = 
   ('2008-02-22 05:58:44.000')::DATETIME YEAR TO SECOND::DATE;

A direct string-to-DATE cast can succeed only if the string specifies a valid DATE value.