Date Data Types

The third major category of data types is the DATE data type. As with numeric data types, Oracle stores all
dates in a single internal representation. However, the format used for entering and returning dates is
determined by the NLS_DATE_FORMAT value for the instance. You can set this value in the initialization file
for the instance, or you can change it with an ALTER SESSION command for an individual database session.
The default format used by into Oracle for dates is DD-MMM-YY HH:MI:SS, where DD is a two-digit
representation of the day number, MMM is a three-character representation of the month name, and YY
is a two-digit representation of the year. HH, MI, and SS are two-digit representations of hours, minutes,
and seconds, respectively. If you do not specify a time portion of a date, these values default to 0.
Oracle does store dates with four-digit centuries, although you have to use a date format other than the
default format to enter and display four-digit centuries.
The three date data types are as follows:
❑ DATE—Includes the day, month, year, hour, minute and second specification.
❑ TIMESTAMP(n)—Can extend the standard DATE data type with fractional seconds. The number
of decimal places for the fractional seconds component is indicated with the n variable.
❑ TIMESTAMP(n) WITH [LOCAL] TIMEZONE—Includes the time zone of either the database server
or the client (when using the LOCAL keyword).
You can perform simple arithmetic on date values, where integers represent whole days and fractions
represent portions of days. If COLTIME contains a value of December 31, 2003, at 6 P.M., COLTIME + 1
would equal January 1, 2004, at 6 P.M., and COLTIME + .5 would equal January 1, 2004, at 6 A.M.
Oracle9i introduced two new date data types used for date arithmetic.
❑ INTERVAL YEAR TO MONTH — Can store an interval of years and months
❑ INTERVAL DAY TO SECOND — Can store an interval of days, hours and seconds
You can use the TO_DATE function to covert character values into dates. Oracle includes a set of extended date
functions that allow you to manipulate dates, such as creating a date value from character representations of
days, months, and years.