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:
|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? ...