Formatting Dates while using the UCanAccess JDBC driver

Question

When using the UCanAccess driver in Java, I am finding it very difficult to output dates into specific formats. Currently, I am using the PreparedStatement.setDate() method which requires the date to be in the format [yyyy-MM-dd HH:mm:ss]. The resulting output, when a user opens the file up, is also in this format.

EDIT* - I am not using the setDate() method with the formatted date, this method only accepts Java Date objects which have no formatting. What I meant to write was that I am using the setString() method when dealing with a formatted date, but UCanAccess requires this date String to be in the format [yyyy-MM-dd HH:mm:ss]. I am really just trying to figure out how to either feed it a different format or change the format after I have called setString().

So far this has been fine, but now I have a need to deliver the file with dates in various formats such as [MM-dd]. I can do this already by making the resulting Access field type a Memo or Text, but the problem becomes when I need the date to be in that format and still have the resulting Access field type be a Date/Time.

I noticed on the UCanAccess front page, it mentions supporting Access date format but I am not finding any examples anywhere after several Google searches. Anyone know how to format dates using this driver while maintaining the correct Date/Time type?

Thanks in advance!

PreparedStatement.setDate() expects a java.sql.Date parameter which does not have a format (and it's not used for doing "output" either) — a_horse_with_no_name
You are most correct @a_horse_with_no_name, I am using the setDate() method when using Java Dates (just a long, no formatting), and then using the setString() method when setting a date with formatting. This requires the format [yyyy-MM-dd HH:mm:ss]. I was trying to make it so that I can feed it any format or change the format programmatically after calling setString() — ksmark
Don't use setString() for dates. use setDate(). The formatting of dates should be handled when displaying (=reading) the data in your frontend, not when saving it. — a_horse_with_no_name
Well that is the issue here is that I am given formatted dates in the String format and I need to open the formatting up a bit to allow other styles. In addition, I would like to deliver a "finished" Access file where the dates are formatted in the manner desired instead of having to open the file up and make the changes manually. — ksmark

Gord Thompson Gord Thompson · Accepted Answer · 2014-11-14T20:47:11

When you retrieve a Date/Time field value via UCanAccess you get a java.sql.Timestamp object. It contains the Date/Time value, but it does not have a format per se.

If you want to represent that Date/Time value in a particular way you just need to format it to your liking. Probably the simplest way to do that is with java.text.SimpleDateFormat. For example:

try (ResultSet rs = s.executeQuery("SELECT DateJoined FROM Members WHERE MemberID=1")) {
    rs.next();
    System.out.printf(
            "         \"raw\" value (via .toString()): %s%n", 
            rs.getTimestamp(1).toString());
    SimpleDateFormat mmddFormat = new SimpleDateFormat("MM-dd");
    System.out.printf(
            "formatted value (via SimpleDateFormat): %s%n", 
            mmddFormat.format(rs.getTimestamp(1)));
}

gives us

         "raw" value (via .toString()): 2014-01-23 00:00:00.0
formatted value (via SimpleDateFormat): 01-23

(Note that when the UCanAccess page talks about the "Access date format" it is referring to Date/Time literals enclosed in hash marks like this: #11/22/2003 10:42:58 PM#. However, you should almost never need to include date literals in your queries because you should be using a PreparedStatement with appropriate .setTimestamp() parameters.)

Addendum

Similarly, when inserting Date/Time values into the Access database: The formatting of the Date/Time value in Java makes no difference to the way the Date/Time value is stored in Access (provided that it is interpreted correctly), and the way it is displayed in Access is a function of the format settings in Access. For example, if we run the following Java code

try (Statement s = conn.createStatement()) {
    s.executeUpdate(
            "INSERT INTO tblDates " +
            "(ID, mmddyyyy) " +
            "VALUES " +
            "('literal', #12/25/2014#)"
            );
}

SimpleDateFormat mmddyyyyFormat = new SimpleDateFormat("MM/dd/yyyy");
Timestamp mmddyyyyXmas = 
        new Timestamp(mmddyyyyFormat.parse("12/25/2014").getTime());

SimpleDateFormat ddmmyyyyFormat = new SimpleDateFormat("dd/MM/yyyy");
Timestamp ddmmyyyyXmas = 
        new Timestamp(ddmmyyyyFormat.parse("25/12/2014").getTime());

SimpleDateFormat yyyymmddFormat = new SimpleDateFormat("yyyy/MM/dd");
Timestamp yyyymmddXmas = 
        new Timestamp(yyyymmddFormat.parse("2014/12/25").getTime());

try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO tblDates " +
        "(ID, mmddyyyy, ddmmyyyy, yyyymmdd) " +
        "VALUES " +
        "('parameters', ?, ?, ?)"
        )) {
    ps.setTimestamp(1, mmddyyyyXmas);
    ps.setTimestamp(2, ddmmyyyyXmas);
    ps.setTimestamp(3, yyyymmddXmas);
    ps.executeUpdate();
}

and then open the Access database with Windows set to use the default formats for "English (United States)" we see

If we change the format settings in Windows to "English (United Kingdom)" then we see

If we want to use a particular format for one or more fields we need to use a specific Format setting in Access, e.g., with something like this

we see

Formatting Dates while using the UCanAccess JDBC driver

1 Answers