Oracle PL/SQL Programming, 5th Edition

Datetime Conversions

Now that you understand the Oracle database’s array of datetime datatypes, it’s time to look at how you get dates into and out of datetime variables. Human-readable datetime values are character strings such as “March 5, 2009” and “10:30 a.m.”, so this discussion centers around the conversion of datetime values from character strings to Oracle’s internal representation, and vice versa.

PL/SQL validates and stores dates that fall from January 1, 4712 B.C.E. through December 31, 9999 A.D. (Oracle documentation indicates a maximum date of December 31, 4712; run the showdaterange.sql script, available on the book’s web site, to verify the range on your version.) If you enter a date without a time (many applications do not require the tracking of time, so PL/SQL lets you leave it off), the time portion of the value defaults to midnight (12:00:00 a.m.).

The database can interpret just about any date or time format you throw at it. Key to that flexibility is the concept of a date format model, which is a string of special characters that define a date’s format to the database. For example, if your input date happens to be, for example, 15-Nov-1961, then that, rather obviously in this case, corresponds to the date format dd-mon-yyyy. You then use the string 'dd-mon-yyyy' in calls to conversion functions to convert dates to and from that format.

I show examples of several different format models in my conversion discussion, and I provide a complete reference to all the ...