#128 Jackcess returning different values for dates prior to 1582-10-15

[Gord Thompson]

As reported in the UCanAccess forum thread here, I have confirmed that Jackcess is returning different values for dates prior to 1582-10-15 (related to the adoption of the Gregorian calendar).

Date in Access  Returned by Jackcess  Difference in days
--------------  --------------------  ------------------
1582-10-15      1582-10-15                             0
1582-10-14      1582-10-04                            10
1492-01-10      1492-01-01                             9
1392-01-10      1392-01-02                             8

The differences appear to follow the pattern shown in the table of the Wikipedia article here.

[James Ahlborn]

Looks like java's gregorian calendar switches to julian for dates pre- October 15, 1582. probably need to call setGregorianChange(new Date(Long.MIN_VALUE)); on the calendar before doing date stuff.

[James Ahlborn]

So, after a little more investigation, i realized that jackcess is not actually returning the wrong dates. A Date in java is an absolute time in millis. The problem comes when you convert the Date into a String for display. As i mentioned previously, it looks like Access uses a different gregorian changeover than java. so, in order to get your dates to display correctly, you need to use a DateFormat which has been appropriately modified.

The following code should print out the correct date values:

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    ((GregorianCalendar)sdf.getCalendar()).setGregorianChange(new Date(Long.MIN_VALUE\
));

    for(Row row : t) {
      Date d = row.getDate("DateField");
      System.out.println("Date " + d + " " + sdf.format(d));
    }

[James Ahlborn]

So, long story short, i'm pretty sure there isn't a bug in jackcess. i'm trying to determine if jackcess could provides some utility code for formatting dates which would help others to handle this correctly.

[Gord Thompson]

Great, thanks. I have confirmed that the same SimpleDateFormat trick works for Date/Time values returned by UCanAccess, so I'll post that in the other thread as a solution.

[James Ahlborn]

So i added two utility methods DatabaseBuilder.createDateFormat() and DatabaseBuilder.toCompatibleCalendar which can be used to create formatters and calendars which are "Access compatible".

these changes are in trunk and will be in the 2.1.3 release.