How do I insert a BC date into Oracle?

#1

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.

-Murali

#2

Murali says...

Quote:

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?

#3

Murali says...

Quote:

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?

#4

Murali says...

Quote:

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?

#5

Murali says...

Quote:

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?

#6

On Aug 6, 2:28*am, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:

Quote:

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

#7

On Aug 6, 2:28*am, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:

Quote:

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

#8

On Aug 6, 2:28*am, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:

Quote:

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

#9

On Aug 6, 2:28*am, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:

Quote:

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

#10

On Aug 6, 7:59*am, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:

Quote:

On Aug 6, 2:28*am, Geoff Muldoon <geoff.muld... (AT) trap (DOT) 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 --