getString("daterow") returning different format from 10g to 11g

I have a large legacy application which calls resultSet.getString(column) and the column it is calling on is the DATE format in Oracle. This code worked just fine with Oracle 10g client. It would return the following:

'2008-05-19 10:03:56.0'

开发者_如何学运维

However, when I use the Oracle 11g client (the server has not changed) it gives the following:

'2008-05-19 10:03:56'

Now, I know the right way to fix this is by changing the code to NOT use getString for a date function, but it's a ton of code and we are trying to do this without having to do code changes.

Is there any configuration parameter(s) I can use to fix this on the Oracle client side?

I've tried the following and it doesn't even change the format:

Statement stmt = conn.createStatement();
stmt.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI'");

This one took off the seconds, but when running a query it is still using the one with seconds. So I don't think the NSL_DATE_FORMAT update will work.

---

The thread below seems to indicate that the date format is hard coded in the jdbc drivers:

Java: ResultSet getString() differs between environments

If that is true then the only solutions seem to be:

1. Change all the code to use getDate()

2. Change all the queries to use to_char(date, 'YYYY-MM-DD HH24:MI:SS.F')

Does anyone see any other solution?

---

Could you do this in your code:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS.F'

If you have a central point at which Connections are created, you could do it there.

(Here's the source I found so far for Oracle data/time literals. There's probably a clearer one somewhere.)

---

In the client environment, set the environment variable NLS_DATE_FORMAT to the format you need and run your program:

set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS.F

EDIT:

Is it feasible to create a database trigger?

CREATE OR REPLACE TRIGGER data_logon_trigger
    AFTER LOGON
    ON DATABASE

BEGIN
   EXECUTE IMMEDIATE
   'alter session set nls_date_format = ''YYYY-MM-DD HH24:MI:SS.F'' ';
END;

This is a brute force method - it affects every logon to the database.