Datetime Expressions

A datetime expression yields a value of one of the datetime datatypes.

datetime_expression::=

Text description of datetime_expression

A datetime_value_expression 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-2. The three combinations that yield datetime values are valid in a datetime expression.

For example, you can add an interval_value_expression to a start_time. Consider a table SCHEDULE with a column START_TIME. The following statement adds 1 year 2 months to the value of the START_TIME column:

SELECT start_time + INTERVAL '1-2' YEAR TO MONTH FROM schedule;

If you specify AT LOCAL, Oracle uses the current session time zone.

The settings for AT TIME ZONE are interpreted as follows:

• The string '(+|-)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 of 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.