Previous
Previous
 
Next
Next

Datetime and Interval Fields

Both datetimes and intervals are made up of fields. The values of these fields determine the value of the data type. Table: Datetime Fields and Values lists the datetime fields and their possible values for datetimes and intervals.


Tip:

To avoid unexpected results in your operations on datetime data, you can verify the Database and session time zones using DBTIMEZONE and SESSIONTIMEZONE If the time zones have not been set manually, Oracle Database uses the operating system time zone by default. If the operating system time zone is not a valid Oracle time zone, then Oracle uses UTC as the default value.

Datetime Fields and Values

Datetime Field Valid Values for Datetime Data Types Valid Values for Interval Data Types

YEAR

-4712 to 9999 (excluding year 0)

Any positive or negative integer

MONTH

01 to 12

0 to 11

DAY

01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the current NLS calendar parameter)

Any positive or negative integer

HOUR

00 to 23

0 to 23

MINUTE

00 to 59

0 to 59

SECOND

00 to 59.9(n), where 9(n) is the precision of time fractional seconds. The 9(n) portion is not applicable for DATETIME.

0 to 59.9(n), where 9(n) is the precision of interval fractional seconds

TIMEZONE_HOUR

-12 to 14 (This range accommodates daylight saving time changes.) Not applicable for DATETIME or TIMESTAMP.

Not applicable

TIMEZONE_MINUTE

(See note at end of table)

00 to 59. Not applicable for DATETIME or TIMESTAMP.

Not applicable

TIMEZONE_REGION

Query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATETIME or TIMESTAMP.

For a complete listing of all time zone regions, refer to Oracle Database Globalization Support Guide.

Not applicable

TIMEZONE_ABBR

Query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATETIME or TIMESTAMP.

Not applicable


Note: TIMEZONE_HOUR and TIMEZONE_MINUTE are specified together and interpreted as an entity in the format +|- hh:mm, with values ranging from -12:59 to +14:00.