Re: How do I insert a BC date into Oracle?

On Aug 6, 11:19 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Aug 6, 7:59 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Aug 6, 2:28 am, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
>
> > > Murali says...
>
> > > > Hi,
>
> > > > Let's say I want to insert 104000 BC into a date field in an Oracle
> > > > table? How do I do that? Is it as simple as inserting "-104000" into
> > > > the date field or is it more complex than that?
>
> > > > Any help would be appreciated.
>
> > > You can't. The Oracle date data type only goes back to 4712 BC
>
> > > And it should be inserted using a proper format mask, eg.:
>
> > > INSERT INTO some_table(some_date_field)
> > > VALUES(TO_DATE('-4712/01/01', 'syyyy/mm/dd'));
>
> > > Geoff M
>
> > > Does anyone know why they picked 4712 BC?
>
> > That's Julian date 0000001.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Geoff, if you are really interested into the Oracle date history seach
> for THE ORACLE CALENDAR
> copyright 2003 by Peter Gulutzan and Trudy Pelzer.  Oracle has a year
> zero error and its Julian dates do not match those produced by other
> systems such as DB2.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

PS - I forgot to mention if you need to represent dates beyound the range Oracle provides you have to do it yourself. First all dates are basically estimates since the modern calendar was adopted by different countries at different times and other calendars such as the Jewish and Chinese calendars exist. Time values of less than a year become fairly meaningless when you talk 2 or 3 million years BC so a number datatype is probably your best bet.

• Mark D Powell --