Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
A datetime expression yields a value of one of the datetime datatypes.
datetime_expression::=
A datetime_value_expr
can be a datetime column or a compound expression that yields a datetime value. 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 AT
LOCAL
, Oracle uses the current session time zone.
The settings for AT
TIME
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 ALTER
SESSION
statement.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.
See Also:
Oracle9i Database Reference for information on the dynamic performance views |
expr
: If expr
returns a character string with a valid time zone format, Oracle returns the input in that time zone. Otherwise, Oracle returns an error.The following example converts the datetime value of one time zone to another time zone:
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