Date conversion error

Toad for Oracle
1

Message from: Krishna Raja

Hi,

I am importing a table data from an excel sheet. The table is having two date
columns. Among those for one column(date), there is date value 1/1/1900 in excel
which needs to be populated into table. For that, the date value 12/31/1989 is
getting loaded. Please refer the attachment for details.

Data in excel

column 1 coulmn 2 column 3
00005-3898-94 1-Jan-1900 19-Jan-2006
00005-3898-94 20-Jan-2006 31-Dec-9999

Data that is getting loaded

column 1 coulmn 2 column 3
00005-3898-94 12/31/1899 1/19/2006
00005-3898-94 1/20/2006 12/31/9999

I have overcome this by changing the date in excel to text.

But why its getting loaded like this for that year???

Thanks in advance!!

Historical Messages

Author: Krishna Raja B
Date: Wed Jul 21 06:39:27 PDT 2010
Hi,

I am importing a table data from an excel sheet. The table is having two date
columns. Among those for one column(date), there is date value 1/1/1900 in excel
which needs to be populated into table. For that, the date value 12/31/1989 is
getting loaded. Please refer the attachment for details.

Data in excel

column 1 coulmn 2 column 3
00005-3898-94 1-Jan-1900 19-Jan-2006
00005-3898-94 20-Jan-2006 31-Dec-9999

Data that is getting loaded

column 1 coulmn 2 column 3
00005-3898-94 12/31/1899 1/19/2006
00005-3898-94 1/20/2006 12/31/9999

I have overcome this by changing the date in excel to text.

But why its getting loaded like this for that year???

Thanks in advance!!

__

2

That’s a well-known quirk of Excel, here’s one resource on the
subject

http://www.exceluser.com/explore/earlydates.htm