A datetime expression yields a value of one of the datetime datatypes.
expr is any expression, except a scalar subquery expression, that evaluates to a value of datatype
DATE datatype is not supported. If this
expr is itself a
datetime_expression, then it must be enclosed in parentheses.
Datetimes and intervals can be combined according to the rules defined in Table 2-5. The three combinations that yield datetime values are valid in a datetime expression.
If you specify
LOCAL, then Oracle uses the current session time zone.
The settings for
ZONE are interpreted as follows:
'(+|-)HH:MM' specifies a time zone as an offset from UTC.
DBTIMEZONE: Oracle uses the database time zone established (explicitly or by default) during database creation.
SESSIONTIMEZONE: Oracle uses the session time zone established by default or in the most recent
time_zone_name: Oracle returns the
datetime_value_expr in the time zone indicated by
time_zone_name. For a listing of valid time zone names, query the
V$TIMEZONE_NAMES dynamic performance view.
Note:Timezone region names are needed by the daylight saving feature. The region names are stored in two time zone files. The default time zone file is a small file containing only the most common time zones to maximize performance. If your time zone is not in the default file, then you will not have daylight saving support until you provide a path to the complete (larger) file by way of the
expr returns a character string with a valid time zone format, then Oracle returns the input in that time zone. Otherwise, Oracle returns an error.
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'America/Los_Angeles' "West Coast Time" FROM DUAL; West Coast Time ------------------------------------------------ 01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES