Java: ResultSet getString() differs between environments

I have a SQL query that is returning an oracle Date object. e.g.:

SELECT sysdate FROM DUAL

There is code currently that does the following:

String s = rs.getString("sysdate");

The problem is, this returns different date format on different environments (database is the same).

One environment will return: 2011-01-31 12:59:59.0

The other will return something weirder: 2011-1-31 12.15.32.0 (the time is separated by decimals)

Maybe this has something do with Locale... one machine is "English (Canada)" as reported by java, the other is "English (United States)".

What I'm interested in is, when the resultset transforms the date object into a string, where is that format coming from?

From Oracle's site:

At database connection time, the JDBC Class Library sets the server NLS_LANGUAGE and NLS_TERRITORY parameters to correspond to the locale of the Java VM that runs the JDBC driver

So yes, the difference in the response is because the machines have a different locale specified. The correct solution should be to use getDate() or getTimestamp() or have the database server return the date as a string in a specific format as mentioned earlier.

I suggest not calling getString at all. The value you've asked for isn't a string, it's a date or timestamp. So call getDate or getTimestamp instead. (I don't know which of them is most appropriate offhand; it depends on the exact semantics of sysdate.)

If you then need to format it, you can do so in an appropriate manner.

Fundamentally, the fewer text conversions you introduce between your code and the database, the better. That's one reason to use parameterized queries - you don't have to care about how the database will parse numeric values or dates and times; you just provide the value. This is the same sort of thing, but in reverse.

In the 11g drivers the format seems to be hard-coded. Calling getString() on the result set ultimately calls this:

oracle.sql.TIMESTAMPTZ.toString(int year, int month, int day, int hours, int minutes, int seconds, int nanos, String regionName)

For dates, oracle.jdbc.driver.DateAccessor.getString() calls this with nanos=-1 and the result uses the format "yyyy-mm-dd HH:MM:SS"

For Timestamps, the format is "yyyy-mm-dd HH:MM:SS.S". Up to 9 digits of nanoseconds will be included.

Actually another interesting result is that this has changed significantly from 10g to 11g:

10.2.0.5.0:

select a DATE value and use getString to read it

2009-04-20 00:00:00.0

select a TIMESTAMP value and use getString to read it

2010-10-15.10.16. 16. 709928000

11.2.0.2.0:

select a DATE value and use getString to read it

2009-04-20 00:00:00

select a TIMESTAMP value and use getString to read it

2010-10-15 10:16:16.709928

Could your environments be using different versions of the Oracle JDBC drivers?

If you are really wanting it as a String you are better off using to_char in the query.

SELECT to_char(sysdate, 'MM/DD/YYYY') FROM DUAL; 

This will be consistent.

Don't use getString, use getDate (or getTimestamp). That way it will won't rely on the database engine to convert it to a string.