Displays same DATA_TYPE value for TIMESTAMP and DATE, while using ojdbc6

3262480

Hi ,

I'm facing an issue when I upgrade my ojdbc14.jar to ojdbc6.jar

Issue :

          data_type value for Date and Timestamp is same  ie., 93 if we use ojdbc6.jar.

          data_type value for Date and Timestamp is different as 91 and 93, if we use ojdbc14.jar.

          My Batch job fetches the data_type of the Oracle 11g TABLE and inserts value according to the data_type value it fetches.

          I have tried using ResultSetMetaData and DatabaseMetaData to get data_type value using getMetadata() method.

          Is there anyway to differentiate the Date and Timestamp data_type value using ojdbc6.jar?.

        

         Please Clarify.

Regards,

Raghu

Unknown

I'm facing an issue when I upgrade my ojdbc14.jar to ojdbc6.jar
Issue :
          data_type value for Date and Timestamp is same  ie., 93 if we use ojdbc6.jar.
          data_type value for Date and Timestamp is different as 91 and 93, if we use ojdbc14.jar.

          My Batch job fetches the data_type of the Oracle 11g TABLE and inserts value according to the data_type value it fetches.
          I have tried using ResultSetMetaData and DatabaseMetaData to get data_type value using getMetadata() method.

          Is there anyway to differentiate the Date and Timestamp data_type value using ojdbc6.jar?.

The Oracle JDBC FAQ explains the issue that existed in the older JDBC driver and how Oracle fixed the problem.

Oracle JDBC Frequently Asked Questions

What is going on with  DATE and  TIMESTAMP?  
This section is on  simple data types. :-)  
Prior to 9.2, the Oracle JDBC drivers mapped the  DATE SQL type to  java.sql.Timestamp. This made a certain amount of sense because the Oracle  DATE SQL type contains both date and time information as does  java.sql.Timestamp. The more obvious mapping to  java.sql.Date was somewhat problematic as  java.sql.Date does not include time information. It was also the case that the RDBMS did not support the  TIMESTAMP SQL type, so there was no problem with mapping  DATE to  Timestamp.  
In 9.2  TIMESTAMP support was added to the RDBMS. The difference between  DATE and  TIMESTAMP is that  TIMESTAMP includes nanoseconds and  DATE does not. So, beginning in 9.2,  DATE is mapped to  Date and  TIMESTAMP is mapped to  Timestamp. Unfortunately if you were relying on  DATE values to contain time information, there is a problem.  
There are several ways to address this problem in the 9.2 through 10.2 drivers:
. . .
Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to  java.sql.Timestamp by default. 

Read the entire FAQ.

What 'differentiation' are you talking about? For Oracle the ONLY difference between date and timestamp is, as the quote says, that timestamp includes nanoseconds.

If you need more help than the above then you need to SHOW US (not just tell us):

1. WHAT you do - post the actual code that supports the statements you made in your post

2. HOW you do it

3. WHAT results you get - show us the results