JDBC 11g: Timestamp to Date Mapping (Doc ID 1097386.1)

Last updated on MARCH 28, 2016

Applies to:

JDBC - Version 11.1.0.6 to 11.2.0.4.0 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Goal

In JDBC 10g, there was the property V8Compatible which allowed for Timestamp to Date conversion. This property has now been deprecated in 11g and should no longer be used.

When inserting a Timestamp value into a Date column, and then selecting this inserted value from the database you will see that the inserted value is no longer identical with the stored value when using the 11g driver.

Running the following example, clarifies this. Here we see the results from running the same example using different drivers.

Using oracle.jdbc.V8Compatible set to true, the results returned for the 10g driver is:

[myuser@myhost C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\10.2.0.4\ojdbc14.jar" TestDate.java

[myuser@myhost C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\10.2.0.4\ojdbc14.jar" TestDate
Java Version: 1.5.0_19
URL: jdbc:oracle:thin:@//dbhost.oracle.com:1522/orcl
DB version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
JDBC Version: Oracle JDBC driver - 10.2.0.4.0
Encoding:
... About to exec: select sys_context('USERENV','LANGUAGE') from dual

Row 1 : AMERICAN_AMERICA.WE8ISO8859P1
Dropping/Creating table...
execSQL() sql=: DROP TABLE TEST_DATE

execSQL() returning: 0 for SQL=DROP TABLE TEST_DATE
execSQL() sql=: CREATE TABLE TEST_DATE (DATE_FIELD Date)

execSQL() returning: 0 for SQL=CREATE TABLE TEST_DATE (DATE_FIELD Date)
Preparing stmt: INSERT INTO TEST_DATE (DATE_FIELD) VALUES (?)
Inserting ...
1



When using the 11g driver, the results returned on comparison are different:

[myuser@myhost C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc5.jar" TestDate
Java Version: 1.5.0_19
URL: jdbc:oracle:thin:@//dbhost.oracle.com:1522/orcl
DB version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
JDBC Version: Oracle JDBC driver - 11.2.0.1.0
Encoding:
... About to exec: select sys_context('USERENV','LANGUAGE') from dual

Row 1 : AMERICAN_AMERICA.WE8ISO8859P1
Dropping/Creating table...
execSQL() sql=: DROP TABLE TEST_DATE

execSQL() returning: 0 for SQL=DROP TABLE TEST_DATE
execSQL() sql=: CREATE TABLE TEST_DATE (DATE_FIELD Date)

execSQL() returning: 0 for SQL=CREATE TABLE TEST_DATE (DATE_FIELD Date)
Preparing stmt: INSERT INTO TEST_DATE (DATE_FIELD) VALUES (?)
Inserting ...
0



Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms