O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required