Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to handle different date formats ?

843854Mar 25 2004 — edited Mar 25 2004
I'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)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 22 2004
Added on Mar 25 2004
9 comments
813 views