Oracle uses its own internal format to store dates.
Date data is stored in fixed-length fields of seven bytes each,
corresponding to century, year, month, day, hour, minute, and second.
Julian dates allow continuous dating by the number of days
from a common reference. (The reference is 01-01-4712 years
BCE, so current dates are somewhere in the 2.4 million range.)
A Julian date is nominally a noninteger, the fractional part
being a portion of a day. Oracle uses a simplified approach
that results in integer values. Julian dates can be calculated
and interpreted differently. The calculation method used by
Oracle results in a seven-digit number (for dates most often
used), such as 2449086 for 08-APR-93.
It's a bit confusing: although the dates are stored in that special 7-byte format, date arithmetic behaves as if the date/time combination is number (Julian date including fractions of day) - so you can:
Select sysdate - trunc(sysdate) from dual; --gives the time as a fraction of a day since midnight.
Select sysdate + 1 from dual; --tells you what tommorow is.
(BTW: since version 8 or 8i, the upper limit is 31-DEC-9999 AD)
Reply With Quote
Bookmarks