#799 Excel Date Export <1900-03-01

3.0.2
closed
Unknown (89)
5
2009-12-01
2009-10-15
sfst
No

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.

Discussion

  • mironsadziak

    mironsadziak - 2009-11-03

    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.

     
  • Gerd Wagner

    Gerd Wagner - 2009-12-01

    Fixed in CVS by Patch ID 2905218

     
  • Gerd Wagner

    Gerd Wagner - 2009-12-01
    • status: open --> closed
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks