Java – NLS_DATE_FORMAT with JDBC

datejavajdbcoracle

I tried to set NLS_DATE_FORMAT inside jdbc, it didn't seem to have any effect.
My code :

//...
Connection conn = ods.getConnection();
Statement stat = conn.createStatement();

stat.execute("alter session set NLS_DATE_FORMAT='YYYY-DD-MM'");
ResultSet rs = stat.executeQuery("select date_column from test_table");

System.out.println(rs.getString(1));   // *** new format not effective here ***
//...

After some reading. I understand that NLS_DATE_FORMAT is hard coded in JDBC Drivers. is this correct ? conclusion made from this :

Language and Territory

The Thin driver obtains language and territory settings (NLS_LANGUAGE and NLS_TERRITORY) from the Java locale in the JVM user.language property. The date format (NLS_DATE_FORMAT) is set according to the territory setting.

So I tried this :

Locale.setDefault(new Locale("en", "US"));
Connection conn = ods.getConnection();
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("select date_column from test_table");

System.out.println(rs.getString(1));   // *** JVM format not effective here

But it's not working. Instead of getting something like

10-OCT-15 (NLS_DATE_FORMAT for US = 'DD-MON-RR')

I get

2015-10-10 00:00:00.0

Using ORACLE 11g, Oracle jdbc drive 12.1, java 8

Best Answer

NLS support from the JDBC driver was dropped in Oracle 10g. Regardless, I'd recommend that you not lean on the Oracle-specific NLS functionality to format your dates.

To format a date retrieved from any database in Java, do this:

Connection conn = ods.getConnection();
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("select date_column from test_table");

DateFormat format = new SimpleDateFormat('yyyy-dd-MM');
System.out.println(format.format(rs.getDate(1));

Note that an instance of SimpleDateFormat is reusable but not thread safe.

If you're using Java 8, you might want to use the new and improved Java Time API instead. Unfortunately, ResultSet hasn't been updated to return an instance of DateTime yet, so you have to convert. Check out DateTimeFormatter and this question about how to convert from old to new. Among other advantages, DateTimeFormatter is fully thread safe.

The format patterns for both the old and new style are the same; here are the docs for the new one.

Related Topic