Oracle PL/SQL Programming, Third Edition

Chapter 10. Dates and Timestamps

Most of our 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 offer us lots of help in handling date information.

First of all, both the RDBMS and PL/SQL provide you with a set of true datetime datatypes that store both date and time information using a standard, internal format. No matter how you choose to represent datetime values when you enter them or display them, such values are represented in a consistent manner within PL/SQL and the RDBMS.

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

Year

Month

Day

Hour

Minute

Second

Fractional second

Time zone hour displacement

Time zone minute displacement

Time zone region name

Time zone abbreviation

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. Oracle provides us with a comprehensive suite of functions with which to manipulate date and time information. Need to convert a character string to a date? No problem. Oracle has you covered with the TO_DATE function, which can interpret and validate a variety of different date formats. Need to convert times between time zones? ...