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

Both of these LET statements assign the year, month, and day information from the DATETIME value to the local SPL variable my_date of type DATE.

Similarly, you can explicitly cast 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 = ('2005-02-22 05:58:44.000')::DATETIME

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 = ('2005-02-22 05:58:44.000');
LET my_date = ('2005-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 = ('2005-02-22 05:58:44.000')::DATETIME::DATE;

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

Related Information