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 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.