The CAST function

With respect to date and time, you can use the CAST function as follows:

· To convert a character string into a datetime value

· To convert a datetime value into a character string

· To convert one datetime type (e.g., DATE) into another (e.g., TIMESTAMP)

When used to convert datetimes to and from character strings, CAST respects the following NLS parameter settings:

NLS_DATE_FORMAT

When casting to or from a DATE

NLS_TIMESTAMP_FORMAT

When casting to or from a TIMESTAMP or a TIMESTAMP WITH LOCAL TIME ZONE

NLS_TIMESTAMP_TZ_FORMAT

When casting to or from a TIMESTAMP WITH TIME ZONE

The following example shows a representative of each type of CAST that is relevant when working with datetimes:

DECLARE a TIMESTAMP WITH TIME ZONE; b VARCHAR2(40); c TIMESTAMP WITH LOCAL TIME ZONE;BEGIN a := CAST ('24-Feb-2002 09.00.00.00 PM US/Eastern' AS TIMESTAMP WITH TIME ZONE); b := CAST (a AS VARCHAR2); c := CAST (a AS TIMESTAMP WITH LOCAL TIME ZONE); DBMS_OUTPUT.PUT_LINE(a); DBMS_OUTPUT.PUT_LINE(b); DBMS_OUTPUT.PUT_LINE(c);END;

The output is:

24-FEB-02 09.00.00.000000 PM US/EASTERN24-FEB-02 09.00.00.000000 PM US/EASTERN24-FEB-02 09.00.00.000000 PM

This example generates a TIMESTAMP WITH TIME ZONE from a character string, converts that value to a VARCHAR2, and finally converts it to a TIMESTAMP WITH LOCAL TIME ZONE. Note that no time zone conversion is done when the value is CAST from TIMESTAMP WITH TIME ZONE to TIMESTAMP WITH LOCAL TIME ZONE. In that case, the time zone information is lost.

In a SQL statement, you can specify the size of a datatype in a CAST, as in CAST (x AS VARCHAR2(40)). However, PL/SQL does not allow you to specify the size of the target datatype.