Oracle PL/SQL Programming, 5th Edition

CAST and EXTRACT

CAST and EXTRACT are standard SQL functions that are sometimes useful when working with datetimes. CAST made its appearance in Oracle8 Database as a mechanism for explicitly identifying collection types, and it was enhanced in Oracle8i Database to enable conversion between built-in datatypes. With respect to date and time, you can use CAST to convert datetime values to and from character strings. The EXTRACT function introduced in Oracle9i Database allows you to pluck an individual datetime element from a datetime or interval value.

The CAST Function

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

• Convert a character string to a datetime value.

• Convert a datetime value to a character string.

• Convert one datetime type (e.g., DATE) to another (e.g., TIMESTAMP).

When used to convert datetimes to and from character strings, CAST respects the NLS parameter settings. Check your settings by querying V$NLS_PARAMETERS, and change them with an ALTER SESSION command. The NLS settings for datetimes are:

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 illustrates the use of CAST for each of these datetime types. The example assumes the default values of 'DD-MON-RR', 'DD-MON-RR HH.MI.SSXFF AM', and 'DD-MON-RR HH.MI.SSXFF AM TZR' for NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT, ...