Oracle in a Nutshell

DATE datatype

Oracle stores all dates and times in a standard internal format that includes the year, month, day, hour, minute, and second. The default format for inserting dates is DD-MON-RR, where RR automatically assigns years in the 50-99 range as 1950-1999 and assigns other years to the 2000-2049 range. You can change the format you use for inserting dates for an instance by changing the NLS_DATE_FORMAT initialization parameter for that instance. You do this for a session by using the ALTER SESSION SQL statement. To change the format for a specific value, you can include the appropriate parameters with the TO_DATE expression in your SQL statement.

Date and Time Ranges

All datetime and interval datatypes described in this appendix are made up of the following fields:

YEAR

Can be between -4712 and 9999 (except year 0) for datetimes; can be any positive or negative number for intervals

MONTH

Can be between 01 and 12 for datetimes; can be between 0 and 11 for intervals

DAY

Is limited by the particular month for datetimes

HOUR

Can be between 00 and 12 for datetimes; can be between 0 and 12 for intervals

MINUTE

Can be between 00 and 59 for datetimes; can be between 0 and 59 for intervals

SECOND

Can be between 00 and 59.9 for datetimes; can be between 0 and 59.9 for intervals

TIMEZONE_HOUR

Can be between 12 and 13 for datetimes; not valid for intervals

TIMEZONE_MINUTE

Can be between 00 and 59 for datetimes; not valid for intervals

You can use the EXTRACT function (see Chapter 8) to extract the value ...