3

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

Community
  • 1
  • 1
iman
  • 145
  • 2
  • 11
  • 5
    `ResultSet.getString()` on a `DATE` column will never honor the DBMS specific "NLS settings". It merely calls `java.sql.Timestamp.toString()` on the date value obtained from the database. But relying on any implicit data type conversion is a bad idea to begin with. Either use `getTimestamp()` and use a `SimpleDateFormat` to apply the format you want in your Java code or use `to_char()` in the SELECT statement to let Oracle format it. – a_horse_with_no_name Apr 15 '16 at 14:06
  • @a_horse_with_no_name I want to believe that toString() is called. But when checking the doc for Data.toString : Formats a date in the date escape format yyyy-mm-dd. It's not really the format that I got ? what do you think ? Thanks – iman Apr 15 '16 at 15:18
  • `java.sql.Date.toString()` returns that format - it's built in. You can try for yourself `System.out.pringln(new java.sql.Date(0).toString())`. The same is true for `java.sql.Timestamp` – a_horse_with_no_name Apr 15 '16 at 15:58
  • FWIW, the only place I can find the quoted documentation is in the docs for Oracle 8i. You need to be including "11g" in your documentation searches. :) – JakeRobb Apr 15 '16 at 16:09

1 Answers1

4

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.

Community
  • 1
  • 1
JakeRobb
  • 1,367
  • 1
  • 12
  • 27
  • 1
    A compliant JDBC 4.2 driver will need to support the `java.time` API through [`getObject(int, Class)`](http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getObject-int-java.lang.Class-) and related methods. So `ResultSet.getObject(1, LocalDate.class)` would need to work. The decision was made to not further expand the API with `getLocalDate` etc methods. – Mark Rotteveel Apr 15 '16 at 14:39
  • @JakeRobb I know all of this. But I'm not allowed to use getDate(). I need to retreive all my data as a string. I performed some new tests. Actually : stat.executeQuery("INSERT INTO tesy_table values ("10-OCT-15") is working just fine. This means that JDBC rely on JVM NLS_DATE_FORMAT. But I don't know why it's not working for SELECT. – iman Apr 15 '16 at 15:05
  • 1
    @iman: "*I need to retrieve all my data as string*" - no offence, but that is stupid. – a_horse_with_no_name Apr 15 '16 at 15:59
  • I'd love to know why you need to retrieve all of your data as a string. What is this, a school assignment? That said, you can use a different SimpleDateFormat (using a pattern matching the 10-OCT-15 format) to parse your string into a date, and then another SDF to format it the way you want. – JakeRobb Apr 15 '16 at 16:02