Year 2000 Error Selecting via Transparent Gateway

All,

        I've discovered a year 2000 related problem when selecting data from our MVS DB2 mainframe using SQL*Plus and transparent gateway. I would appreciate all comments.

        All looks good on the mainframe/DB2 side. Everything also looks good on the Oracle side, too. I also have an Oracle view setup against a DB2 table
when looks perfectly good, which makes me suspect SQL*Plus 3.3.3.0.0. We are
using Oracle7 7.2.3.0.0.

        The result below illustrates the problem. In this query, dates are selected from a date table only for the 8th week of the year 2000. All data
on the mainframe side is accurate.

SQL> select to_char(to_date(dly_date),'yyyy-mm-dd') from db2conn.dly_date_at_pdb2
where co_cal_wk = 200008;
  2
TO_CHAR(TO_DATE(DLY_DATE),'YYYY-MM-DD')

1900-02-27
1900-02-26
1900-02-25
1900-02-24
1900-02-23
1900-02-22
1900-02-21

7 rows selected.                                  

Note: When I run the same query for the 9th week of the year 2000, I get the
following...

SQL> select to_char(to_date(dly_date),'yyyy-mm-dd') from db2conn.dly_date_at_pdb2
where co_cal_wk = 200009;
  2 ERROR:
ORA-01839: date not valid for month specified ORA-02063: preceding line from PDB2

I've isolated this error to the following cause... There IS a leap year in 2000,
but when an attempt to 'convert' February 29, 2000 to the year 1900, the query
fails because there was no leap year in 1900 -- it is truly an invalid date.

-- 


---------------------------------------------

Paul E. Kilmer - Hershey Foods Corporation
pkilmer_at_hersheys.com
http://www.hersheys.com


---------------------------------------------

Note:  My return e-mail address is doctored
for anti-spam purposes; use the address above.