Monday, November 5, 2007

Oracle Date Format

The Oracle database stores dates in an internal numeric format, representing the century, year, month, day,
hours, minutes, and seconds.
The default display and input format for any date is DD-MON-RR. Valid Oracle dates are between January
1, 4712 B.C. and A.D. December 31, 9999.
In the example in the slide, the HIRE_DATE for the employee Gietz is displayed in the default format DDMON-
RR. However, dates are not stored in the database in this format. All the components of the date and
time are stored. So, although a HIRE_DATE such as 07-JUN-94 is displayed as day, month, and year, there
is also time and century information associated with it. The complete data might be June 7th, 1994 5:10:43
p.m.
This data is stored internally as follows:
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 94 06 07 5 10 43
Centuries and the Year 2000
The Oracle Server is year 2000 compliant. When a record with a date column is inserted into a table, the
century information is picked up from the SYSDATE function. However, when the date column is
displayed on the screen, the century component is not displayed by default.
The DATE data type always stores year information as a four-digit number internally, two digits for the
century and two digits for the year. For example, the Oracle database stores the year as 1996 or 2001, and
not just as 96 or 01.


Date Functions
Date functions operate on Oracle dates. All date functions return a value of DATE data type except
MONTHS_BETWEEN, which returns a numeric value.
• MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2.
The result can be positive or negative. If date1 is later than date2, the result is positive; if date1
is earlier than date2, the result is negative. The noninteger part of the result represents a portion of
the month.
• ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an
integer and can be negative.
• NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char')
following date. The value of char may be a number representing a day or a character string.
• LAST_DAY(date): Finds the date of the last day of the month that contains date.
• ROUND(date[,'fmt']): Returns date rounded to the unit specified by the format model fmt.
If the format model fmt is omitted, date is rounded to the nearest day.
• TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit
specified by the format model fmt. If the format model fmt is omitted, date is truncated to the
nearest day.
This list is a subset of the available date functions. The format models are covered later in this lesson.
Examples of format models are month and year.