Bug in "copy to Excel" for dates before 1900-03-01

[Worker]

select date '1900-02-28', date '1900-03-01' from dual

If you execute that in a SQL window and use right-click -> Copy to Excel, the first cell in Excel shows "2/29/1900".

I believe this is related to this known Excel bug.

The problem does not appear when using right-click -> Export Results -> Excel file.

 

[Marco Kalter]

This works fine for me. Can you let me know your PL/SQL Developer version and your Excel version?

 

[Worker]

That's odd...

PL/SQL Developer 12.0.5.1828
Excel 2013
Windows 10

 

[Gustavo]

Do you export to .xls or .xlsx ?

 

[Gustavo]

For me, it was even worse. PLSD shows 28/02/00 and 01/03/00 in results. After exporting to Excel (both xls and xlsx), I get 28/02/2000 and 01/03/2000.

If I use Export Results (again, both xls and xlsx), I'll get text fields and Excel prompts me to change XX to 19XX or 20XX.

In PLSD, NLS Options for Date is set to Oracle Format.
nls_date_format is DD/MM/RR

As Oracle's parameter was obviously an issue, I tried an alter session set nls_date_format = 'dd/mm/yyyy'. Then I found a new bug. PLSD disregarded the command and results were still shown in previous format.

For the final test, I included the alter session command in AfterConnect.sql and restarted PLSD. Then I reproduced the issue and I got the same results Worker did:
- Export results to either xls or xlsx is ok.
- Copy to Excel to either xls or xlsx is NOT ok.

 

[Worker]

There is no difference between xls and xlsx.

My NLS Options are set to "Windows format", so I assume that ends up as "mm/dd/yyyy".

How it looks in PL/SQL Developer (note "2/28"):

How it looks in Excel (note "2/29"):

 

[Nicolas D.]

Hi,

Excel is wrong.
This is a mistake from date calculation Excel is doing.
Excel assume that 1900 is a leap year but it is not. (It is a very old bug)

The way the date are encoded in Excel format is a number of day since 1/1/1900.
But due to the wrong assumption in Excel, the date between 1/1/1900 and 2/28/1900 are shifted by 1.

The only solution would be for Marco to assume automatically in the export that any date between 1/1/1900 and 2/28/1900 to reduce the calculated value by 1. Any date before 1900 are treated as a string as Excel do not support date before that point.

See:https://en.wikipedia.org/wiki/Leap_year_bug :
Microsoft Excel has, since its earliest versions, incorrectly considered 1900 to be a leap year, and therefore that February 29, 1900 comes between February 28 and March 1 of that year. The bug originated from Lotus 1-2-3, and was purposely implemented in Excel for the purpose of backward compatibility. Microsoft has written an article about this bug, explaining the reasons for treating 1900 as a leap year.[6] This bug has been promoted into a requirement in the Ecma Office Open XML (OOXML) specification.[7][8]