JDBC Adapter Oracle date problem

, ,
1

Folks,

I am using JDBC Adapter 6.0.3.1 with FP1 Fix 11.

Database is Oracle 10g (10.1.0.4) and ojdbc14 (10.1.0.4)

I have connection with v8 compatable true so I can insert timestamps to date columns. I am doing an adapter insert to a DATE column with the input type as string.

The problem is when inserting date to a date field certain dates are having the century incorrectly inserted to the database. For example if I pass a date 1969-11-30 00:00:00 it works fine but 1949-11-30 00:00:00 saves in the database as 2049-11-30 00:00:00

Oracle says there is a Y2K bug and the syntax TO_DATE (‘1949-11-30’,‘rrrr-mm-dd’) should work. i.e. rrrr replaces yyyy as a workaround to the bug. (Y2k bug in software released 2005???)

Of course I’m not using the TO_DATE function as I am doing an adapter insert rather than custom SQL.

Has anyone else seen this? Are there any other likely explanations? Do I really have to hand code all my SQL’s to Oracle databases? Are there any other workarounds?

Love to hear from any Oracle Gurus or JDBC adapter PD :slight_smile:

2

P.S. - I tried the ‘rrrr’ format in custom SQL but it didn’t work. It seems most likely this is a problem with the actual database but I’m still open to suggestions and whether anyone else has come accross this…

Cheers.

3

Niall,

  1. I think the correct format is uppercase ‘RRRR’ & ‘YYYY’
  2. You can still use the TO_DATE in the JDBC Adapter. Simply add it into the field name.

Allen.

4
  1. It is often useful to test your assertions using SQL commands in sqlplus to determine whether it is a database configuration issue. If it works fine as SQL it should work in the Adapter.
5

Thx -

I did try various clients, TOAD, DBVis, Adapter, SQL plus and from various machines includig the server itself. Other date fields in the database work fine. So it’s not a database wide problem nor does it seem to be driver/client related.

This particular table has a lot of triggers (16) 9 of which reference or operate on this particular date field. It’s not immediately apparant in the triggers that there is date formatting happening sending the data askew. There are some oracle folks looking at it now - I thought I’d cast the net see if anyone else had seen something similar.