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
I show examples of several different format models in my conversion discussion, and I provide a complete reference to all the ...