Re: Internal date format (numerical)

Guy wrote:

> Yes. I also figured out that the base date for all internal date format
> seems to be '30-12-1899', for oracle
>
> Its 1-1-1900 for Sql Server. Which seems logical.
>
> Wonder how Oracle could came up with a date "base" like that?
>

Seems logical, but isn't. Wonder no more...

The method of expressing date/time as the number of seconds since a "base" time (e.g. midnight Jan 1, 1970 UTC) is what most operating systems use (I think Unix was the first). The typical requirements for storing and efficiently manipulating date/time values in a database far exceed what is provided by most operating systems.

Microsoft SQL Server employs a variation of the simplistic OS method by storing dates as the number of days before or after a base date, which in its case is January 1, 1900. Values for dates earlier than January 1, 1753, are not permitted (how convenient for avoiding the need to deal with Julian/Gregorian calendar issues, how inconvenient for storing historical data).

Oracle uses its own proprietary method which allows for dates from 4713BC (Julian day number 0) to 9999AD and which uses Year 0 as a base year.[1]

If you wish to follow up on the excellent answer and advice you received from ++mcs, check out the following documents:

Oracle Server Date Handling (An Oracle White Paper November 1999 Version 1.10).

Note:149118.1 Oracle9i Datetime and Interval data type feature

[1]The following document explains Oracle's handling of Julian/Gregorian cutover issues, plus Oracle's Year 4713BC bug and Year 0 bug:

http://www.orafaq.net/papers/dates_o.doc

-Mark Bole Received on Wed Nov 30 2005 - 14:34:54 CST