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.
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:
When casting to or from a DATE
When casting to or from a TIMESTAMP or a TIMESTAMP WITH LOCAL TIME ZONE
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 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, ...