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