CODEWITHSUNDEEP
javaandroidms-access
Bolic
Bolic

Reputation: 745

Convert Access 2003 date number (integer) to Java Date

I am trying to use a timestamp from an Access 2003 database in my Android application. Through a webserver I get a long of the date field which fieldsize is "long integer" and the format "date, short".

The long is looking like: 41574

How can I convert this long to an date/long which I can use with the class Date in Java/Android?

Upvotes: 0

Views: 202

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123819

It can be risky to "roll your own" procedures to manipulate the internal representations of things like dates. Eric Lippert wrote an excellent blog entry here that relates specifically to the date format used by Access (and VBA, and VBScript).

The best solution would be to fix the web service so that it passed an actual unambiguous date value like yyyy-mm-dd. However, if that was not possible then I would be inclined to use the following:

import java.text.ParseException;
import java.text.SimpleDateFormat;

public class dateTestMain {

    public static void main(String[] args) {
        long accessDateNumberToTest;
        for (accessDateNumberToTest = -2; accessDateNumberToTest <= 2; accessDateNumberToTest++) {
            long ms_since_1970_01_01 = (accessDateNumberToTest - 25569) * 86400000;
            java.util.Date utcDate = new java.util.Date(ms_since_1970_01_01);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            // dump date to string as UTC...
            sdf.setTimeZone(java.util.TimeZone.getTimeZone("UTC"));
            String utcDateString = sdf.format(utcDate);

            // ...and parse back to Date in local timezone
            sdf.setTimeZone(java.util.TimeZone.getDefault());
            java.util.Date jDate = null;
            try {
                jDate = sdf.parse(utcDateString);
                System.out.println(String.format("Java test: %s maps to %s", accessDateNumberToTest, jDate));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
    }

}

The for loop in the above code just tests a range of Access date "numbers" to verify that the conversion will properly handle the transition from negative to positive. The Java code returns the following...

Java test: -2 maps to Thu Dec 28 00:00:00 EST 1899
Java test: -1 maps to Fri Dec 29 00:00:00 EST 1899
Java test: 0 maps to Sat Dec 30 00:00:00 EST 1899
Java test: 1 maps to Sun Dec 31 00:00:00 EST 1899
Java test: 2 maps to Mon Jan 01 00:00:00 EST 1900

...and an Access VBA test using CDate(num) seems to agree

Access VBA test: -2 maps to 1899-12-28 00:00:00
Access VBA test: -1 maps to 1899-12-29 00:00:00
Access VBA test: 0 maps to 1899-12-30 00:00:00
Access VBA test: 1 maps to 1899-12-31 00:00:00
Access VBA test: 2 maps to 1900-01-01 00:00:00

Also, unlike numbers formatted as dates in Excel spreadsheet cells, the number 60 does not map to 1900-02-29. (That is not a valid date because 1900 was not a leap year. The behaviour in Excel is the legacy of an earlier Lotus 1-2-3 bug.) Once again the Java results...

Java test: 58 maps to Mon Feb 26 00:00:00 EST 1900
Java test: 59 maps to Tue Feb 27 00:00:00 EST 1900
Java test: 60 maps to Wed Feb 28 00:00:00 EST 1900
Java test: 61 maps to Thu Mar 01 00:00:00 EST 1900
Java test: 62 maps to Fri Mar 02 00:00:00 EST 1900

...agree with the Access VBA test:

Access VBA test: 58 maps to 1900-02-26 00:00:00
Access VBA test: 59 maps to 1900-02-27 00:00:00
Access VBA test: 60 maps to 1900-02-28 00:00:00
Access VBA test: 61 maps to 1900-03-01 00:00:00
Access VBA test: 62 maps to 1900-03-02 00:00:00

Upvotes: 1

Related Questions