Date formatting using the JCBC UCanAccess driver

Question

When using the UCanAccess driver in Java, it is very difficult for me to output dates in certain formats. I am currently using the PreparedStatement.setDate () method, which requires the date to be in the format [yyyy-MM-dd HH: mm: ss]. The result, when the user opens the file, is also in this format.

EDIT * - I do not use the setDate () method with a formatted date, this method only accepts Java Date objects that do not have formatting. I wanted to write that I use the setString () method when working with a formatted date, but UCanAccess requires that this String date be in the format [yyyy-MM-dd HH: mm: ss]. I'm just trying to figure out how to either file it in a different format, or change the format after I called setString ().

This has been fine so far, but now I need to deliver the file with dates in various formats, such as [MM-dd]. I can do this already by creating an accessible field of type Memo or Text as a result, but the problem arises when I need a date that will be in this format and still have the type of access field as the date / time.

I noticed on the UCanAccess main page, it mentions support for the access date format, but I have not found any examples anywhere after several Google searches. Does anyone know how to format dates using this driver while maintaining the correct date / time type?

Thanks in advance!

Answer 1

When you retrieve the value of the Date / Time field through UCanAccess, you get a java.sql.Timestamp object. It contains a Date / Time value, but it does not have a format as such.

If you want to present this Date / Time value in a specific way, you just need to format it to your liking. Probably the easiest way to do this is with java.text.SimpleDateFormat . For instance:

 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 says “access date format”, this refers to date / time literals enclosed in hash marks as follows: #11/22/2003 10:42:58 PM# . However, you almost don't you will need to include date .setTimestamp() in yours because you must use PreparedStatement with the appropriate .setTimestamp() parameters.)

Adding

Similarly, when inserting date / time values ​​into an Access database: formatting the Date / Time value in Java has nothing to do with how the Date / Time value is stored in Access (provided that it is interpreted correctly) and the way it is displayed in Access is This is a function of the format options 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 to use the standard formats for "English (United States)", we see

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

If we want to use a specific format for one or more fields, we need to use a specific Format parameter in Access, for example, with something like this

we see that