how to handle different date formats ?
843854Mar 25 2004 — edited Mar 25 2004I'm currently writing an application that has to be compatible with different database backends.
But I have some problems with the date/time fields when using Oracle as a db backend:
After searching the forums, I found a solution for my "inserts" problem:
// This is needed because otherwise the db session will be reset with each jdbc call,
// ending the session and nullifying the ALTER SESSION command
con.setAutoCommit( false );
// set the date/time format to what we want it to be
result = stmt.executeQuery("ALTER SESSION SET NLS_DATE_FORMAT = 'dd/mm/yyyy hh24:mi:ss'");
res = stmt.executeUpdate(query);
Where query is something like "INSERT INTO DATE_TEST VALUES ('val1',"val2','val3','25/03/2004 15:59:44');
But when I want to retrieve this data, the timestamp is returned in another format:
result = stmt.executeQuery("SELECT * FROM DATE_TEST");
This gives me "2004-03-25 15:59:44.0" for the timestamp !
when I run exactly the same query in the Oracle SQL*Plus console, I get the correct format:
SQL> SELECT * FROM DATE_TEST;
VAL1 VAL2 VAL3 TIME
---- ---- ---- ----
val1 val2 val3 25/03/2004 15:59:44
SQL>
I already tried the trick with the NLS_DATE_FORMAT before retrieving the data, but that doesn't seem to do anything.
Any ideas how I can manipulate the format of a DATE field for retrieval ?
Note:
This is what I use:
- Oracle 9i, Release 2 (9.2.0.1.0) for Windows (on windows 2000 Server)
- ojdbc14.jar (Oracle thin jdbc driver)
- j2sdk 1.4.2-04 for my application (currently running on windows server 2003 Enterprise, but is also tested on windows 2000, xp, linux, solaris)