Re: Date Format Problem/Internal Date Storage

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 27 Jan 2001 22:01:19 -0600
Message-ID: <lQMc6.362$Yq5.182165@nnrp2.sbc.net>

"SBumford" <sbumford_at_aol.com> wrote in message news:20010127165624.10139.00001316_at_ng-cr1.aol.com...
> Hi all,
>
> I have just experienced a problem with invalid inserted dates in Oracle.
>
> Using the dump statement on the date field in my table, I found that the
year
> byte is 201, whereas I was expecting this to be 101.
>
> I am using a Prepared Statement using JDBC. My conclusion is that the
problem
> is to do with the version of classes111.zip (Oracle JDBC driver classes)
which
> uses the OCI73JDBC.DLL.
>
> The only problem is that I cannot find any documentation of whether this
is a
> bug to do with the above.
>
> I have validated the date that I am passing, but what concerns me most is
the
> fact that the year byte should never be greater than 199, whereas I'm
getting
> 201.
>
> Any knowledge about the problem.
>
> TIA,
>
> Stephen
>

This is not a new problem. If you can't upgrade to a more recent version of the jdbc driver, there are a couple of workarounds.

ALERT: JDBC Drivers May Insert Invalid Year 2000 Dates

JDBC Driver Versions Affected

7.3.4.XX, 8.0.4.XX

Note: The problem does not occur in JDBC Driver Version 8.0.5 and

above.

To determine what JDBC driver version you are using, refer to Note:73629.1 entitled "Example: How to Determine the JDBC Driver Version".

Platforms Affected

GENERIC Problem Description

JDBC OCI and thin drivers incorrectly insert year 2000 dates into Oracle when using java.sql.Timestamp type bind variables.

The following describes the internal date binary format and is an excerpt from the Oracle Call Interface Programmer's Guide, Volume 1, (A67848-01), Chapter 3 Datatypes, in the section on the DATE datatype:

The DATE datatype can update, insert, or retrieve a date value using the

       Oracle internal date binary format contains seven bytes, as shown in
       Table 3-4.

       Table 3-4
      -----------------------------------------------------------------
       Byte           1        2     3      4    5     6       7
      -----------------------------------------------------------------
       Meaning        Century  Year  Month  Day  Hour  Minute  Second

       Example        119      192   11     30   16    18      1
       (for 30-NOV-1992,
        3:17 PM)
      -----------------------------------------------------------------

      The century and year bytes are in excess-100 notation.  Dates Before
      Common Era (BCE) are less than 100.  The era begins on 01-JAN-4712
BCE,
      which is Julian day 1.  For this date, the century byte is 53, and the
      year byte is 88.

The problem with the JDBC drivers is that the century and year is stored as 119,200, rather than the valid 120,100. After year 199, the value should return to 100 and the first byte should increment by 1.

Likelihood of Occurrence

100%, if you are using java.sql.Timestamp for inserting or updating dates into
Oracle via bound values using java.sql.PreparedStatement.setTimestamp().

Possible Symptoms

Values appear as proper year 2000 date strings when selected, but produce unexpected results when used in comparisons and sorts. For example, the following select statement returns NO ROWS, even though there appear to be dates in the year 2000 in the table:

select * from tab where date_col>=to_date('01-01-2000','DD-MM-YYYY')

Workaround

Check and correct invalid date values that are already in the database.

First, detect if any invalid values have been inserted as follows:

       select dump(column_name) from table_name
         where column_name <> column_name + 0;

     Invalid values appear similar to the following:

       Typ=12 Len=7: 119,200,1,1,1,1,1
                     ^^^ ^^^

     instead of the correct:

       Typ=12 Len=7: 120,100,1,1,1,1,1
                     ^^^ ^^^

     If such values exist, correct them using the following script:

       update table_name set column_name = colum_name + 0
         where column_name <> column_name + 0;

     If the source of the corrupt date is difficult to isolate, then
     a row level trigger may be used to test for valid date values before
     the row is inserted into the database.

You can also detect invalid date values using the BADDATE script contained in Note:95402.1 entitled "BADDATE Script to Check Database for Corrupt Dates".

Finally, Note:77661.1 entitled "How to identify a wrong internal date value for Y2K date" documents a more efficient method using indexes to identify invalid date values.

2. Correct the JDBC program.

There are several possible ways to correct the issue with the JDBC program

as follows (choose one):

Download the latest JDBC driver from OTN and use it in place of the older driver in your CLASSPATH for your JDBC program.

JDBC OCI drivers are available on OTN for Windows NT and Solaris, along

with the executable needed for OCI. The JDBC thin drivers are the same

on all platforms except for Windows. The UNIX platform JDBC thin driver

may be under the Solaris link on OTN. The JDBC thin driver for Windows

are listed separately and may be under the Windows NT link.

The URL for the JDBC drivers on OTN is:

http://technet.oracle.com/software/tech/java/sqlj_jdbc/software_index.htm

       For information on how to install JDBC drivers, refer to Note:60825.1
       entitled "Installing JDBC Drivers".

     - Change your program so that you do not bind with java.sql.Timestamp
       type variables, but instead use java.lang.String variables.

     - Change the SQL statements in the JDBC code so that it adds a 0 to the
       column.  For example, if your JDBC SQL statement is:

         PreparedStatement preStmtA =
                    connection.prepareStatement (
                    "UPDATE EMP SET hiredate = ? ");

       change it to the following:

         PreparedStatement preStmtA =
                    connection.prepareStatement (
                    "UPDATE EMP SET hiredate = ? + 0 ");

References

"Oracle Call Interface Programmer's Guide, Volume 1", (A67848-01), the section on DATE in Chapter 3 Datatypes

Note:92403.1 Example: Inserting Dates using Different Java Date Formats Received on Sat Jan 27 2001 - 22:01:19 CST