Oracle PL/SQL Programming, Third Edition

Date and Timestamp Conversions

Now that you understand Oracle’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, 2002” and “10:30 AM”, 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. through December 31, 9999 A.D. (Oracle documentation indicates a maximum date of December 31, 4712; run the showdate.sql script, available on the O’Reilly 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 AM).

Oracle 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 Oracle. Because they form the basis of date conversion, we talk about date format models first, and then show you how to use them to get dates into and out of PL/SQL datetime variables.

Date Format Models

In Versions 6 and earlier of the Oracle RDBMS, the default format for dates as character values was DD-MON-YY, a cause of consternation for many developers and users. While this format is common in many parts of the world, ...