SQuirreL SQL Client

Tracker: Bugs

5 Excel Date Export <1900-03-01 - ID: 2879711

Last Update: Settings changed ( gerdwagner )

Details:

Any date before 1900-03-01 when exported into Excel will shift down one
day, e.g. 1900-01-01 will become 1899-12-31.

If I understand http://support.microsoft.com/kb/214058 correctly, then this
is a bug^H^H^Hfeature of Excel that is here to stay, so it might be better
to emulate this behavior on export (to get the same data shown in Excel as
in SquirreL SQL) .

Workarounds:
(1) export as CVS and import into Excel (but don't use CVS as extension, at
least not with Excel 2003; Open Office will allow you to change the field
delimiter)
(2) Craft the select command so that the data is pulled as string (which
might be generally a good idea to always get the format you need)
(3) use default formatting (which will send the date as string)

As the workarounds outnumber the problem I would give this an ultralow
priority.

Submitted:

sfst ( sfst ) - 2009-10-15 08:40

Priority:

Status:

Closed

Resolution:

None

Assigned:

Rob Manning

Category:

Unknown

Group:

3.0.2

Visibility:

Public

Comments ( 2 )

Date: 2009-12-01 23:01 Sender: gerdwagner Fixed in CVS by Patch ID 2905218
Date: 2009-11-03 14:12 Sender: mironsadziak A solution to this problem could be to modify net.sourceforge.squirrel_sql.fw.gui.action.TableExportCvsCommand.java file and at line 345, in method getXlsCell() add following lines: long utcTime = time+offset; /* Work around Excel's problem with dates before 1900-03-01 * http://support.microsoft.com/kb/214058 * -2203891200000l is 1900-03-01 UTC time * 8640000 means 24 hours */ if (utcTime<-2203891200000l) { utcTime+=86400000; } java.util.Date xlsUTCDate = new java.util.Date(utcTime); I have tried it and seems to work fine for all relevant date/timestamp values. Although generally using here an 'if' seems a little dirty. Some other solution could be running all the exported datatypes through some converters/checkers injected to TableExportCvsCommand.

Date: 2009-12-01 23:01
Sender: gerdwagner

Fixed in CVS by Patch ID 2905218

Date: 2009-11-03 14:12
Sender: mironsadziak

A solution to this problem could be to modify
net.sourceforge.squirrel_sql.fw.gui.action.TableExportCvsCommand.java file
and at line 345, in method getXlsCell() add following lines:

long utcTime = time+offset;
/* Work around Excel's problem with dates before 1900-03-01
* http://support.microsoft.com/kb/214058
* -2203891200000l is 1900-03-01 UTC time
* 8640000 means 24 hours
*/
if (utcTime<-2203891200000l) {
utcTime+=86400000;
}

java.util.Date xlsUTCDate = new java.util.Date(utcTime);

I have tried it and seems to work fine for all relevant date/timestamp
values.
Although generally using here an 'if' seems a little dirty. Some other
solution could be
running all the exported datatypes through some converters/checkers
injected to TableExportCvsCommand.

Attached File

Changes ( 3 )

Field	Old Value	Date	By
close_date	-	2009-12-01 23:01	gerdwagner
allow_comments	1	2009-12-01 23:01	gerdwagner
status_id	Open	2009-12-01 23:01	gerdwagner

SQuirreL SQL Client

Tracker: Bugs

Comments ( 2 )

Attached File

No Files Currently Attached

Changes ( 3 )

About SourceForge

Find Software

Develop Software

Community

Help