Oracle PL/SQL Programming, 4th Edition

Chapter 10. Dates and Timestamps

Most applications require the storage and manipulation of dates and times . Dates are quite complicated: not only are they highly formatted data, but there are myriad rules for determining valid values and valid calculations (leap days and years, national and company holidays, date ranges, etc.). Fortunately, the Oracle RDBMS and PL/SQL provide a set of true datetime datatypes that store both date and time information using a standard, internal format.

For any datetime value, Oracle stores some or all of the following information:

Support for true datetime datatypes is only half the battle. You also need a language that can manipulate those values in a natural and intelligent manner—as actual dates and times. To that end, Oracle provides you with support for SQL standard interval arithmetic, datetime literals, and a comprehensive suite of functions with which to manipulate date and time information.

Datetime Datatypes

For a long time, the only datetime datatype available was DATE. Oracle9i Database shook things up by introducing three new TIMESTAMP datatypes offering significant, new functionality while also bringing Oracle into closer compliance with the ISO SQL standard. Oracle now supports the following, four datetime datatypes:

DATE

Stores a date and time, resolved to the second. Does not include time zone.

Tip

DATE is the original, datetime datatype supported by Oracle, and is the only one available through Oracle8i Database.

TIMESTAMP

Stores date ...