Program for Bad Oracle Date in SQL Stored Procedure

I create below statement in a SQL Stored Procedure in order to extract data from Oracle tables using open query and insert into another SQL table.

SET @MyString = 'SELECT  SUBSTR(GPCOMP1.GPRECL.CUSTNO,1,15) as CUSTNO,  GPCOMP1.GPRECL.AMOUNT, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.TRANTYPE, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD4,1,10) as FLEXFIELD4,
            SUBSTR(GPCOMP1.GPRECL.FLEXFIELD2,1,5) as FLEXFIELD2, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD14,1,35) as FLEXFIELD14, GPCOMP1.GPRECL.RCLDATE, GPCOMP1.GPRECL.PROMISED,
            SUBSTR(GPCOMP1.GPRECL.INVNO,1,10) as INVNO, SUBSTR(GPCOMP1.GPRECL.INVREFNO,1,25) as INVREFNO, GPCOMP1.GPRECL.TRANORIG, GPCOMP1.GPRECL.FLEXNUM3, SUBSTR(GPCOMP1.GPRECL.TRANCURR,1,5) as TRANCURR,
            SUBSTR(GPCOMP1.GPRECL.INVPONUM,1,15) as INVPONUM, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD1,1,10) as FLEXFIELD1, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD9,1,25) as FLEXFIELD9, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD10,1,25) as FLEXFIELD10,
            SUBSTR(GPCOMP1.GPRECL.FLEXFIELD11,1,25) as FLEXFIELD11, SUBSTR(GPCOMP1.GPCUST.COLLECTOR,1,10) as COLLECTOR, SUBSTR(GPCOMP1.GPCOLL.COLLNAME,1,30) as COLLNAME, SUBSTR(GPCOMP1.GPCOLL.TEAM,1,10) as TEAM,
            SUBSTR(GPCOMP1.GPRECL.CUSTNO, 2, 12) AS CUSTNO13, SUBSTR(GPCOMP1.GPRECL.CUSTNO, 2, 6) AS CUSTNO6, SUBSTR(GPCOMP1.GPCUST.COMPANY,1,40) as COMPANY, sysdate as processdate,
            SUBSTR(GPCOMP1.GPRECL.FLEXFIELD5,1,10) as FLEXFIELD5, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD6,1,35) as FLEXFIELD6, SUBSTR(GPCOMP1.GPRECL.FLEXFIELD7,1,35) as FLEXFIELD7,
            GPCOMP1.GPRECL.REASONAR, (CASE WHEN GPCOMP1.GPRECL.FLEXDATE1 <> ''0622/11/20'' THEN GPCOMP1.GPRECL.FLEXDATE1 ELSE SYSDATE END) as FLEXDATE1,  GPCOMP1.GPRECL.TRAN_ID, GPCOMP1.GPRECL.DUEDATE, SUBSTR(GPCOMP1.ARMAST.PNET,1,5) as PNET
             FROM    GPCOMP1.GPRECL, GPCOMP1.GPCUST, GPCOMP1.GPCOLL, GPCOMP1.ARMAST
            WHERE  GPCOMP1.GPCUST.INACTIVE = ''N'' AND GPCOMP1.GPRECL.CUSTNO = GPCOMP1.GPCUST.CUSTNO AND GPCOMP1.GPCUST.COLLECTOR = GPCOMP1.GPCOLL.COLLCODE AND GPCOMP1.GPRECL.CUSTNO = GPCOMP1.ARMAST.CUSTNO AND GPCOMP1.GPRECL.INVNO = GPCOMP1.ARMAST.INVNO '

      SET @MyString = N'INSERT INTO #gprecl
            SELECT  * from openquery
            ([GTPFR],    '''
                      + Replace(@MyString, '''', '''''') + ''')'

      EXEC (@MyString)

The above statement failed recently because of  a bad date in one of the Oracle fields.
I put in below code in order to get the procedure to run without failing.

(CASE WHEN GPCOMP1.GPRECL.FLEXDATE1 <> ''0622/11/20'' THEN GPCOMP1.GPRECL.FLEXDATE1 ELSE SYSDATE END) as FLEXDATE1,  

How can I program this so it will not fail if it is a bad date.
I want it to put in GPCOMP1.GPRECL.FLEXDATE1 if it is a good date and SYSDATE if it is a bad date.
thayduckProgrammer AnalystAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
I understood to original post to mean that you were querying from Oracle and inserting into SQL Server.  isdate() is a SQL server function, not Oracle, so it's not applicable if the query is contained to Oracle.

The safest way is to write your own function to test the date and call it.  You can also test for the value being less than your lower year limit ('1900', '1800', '1750', etc.) but that won't necessarily detect an improperly formatted date (i.e. '19a0/01/01').

Something like this should work.

create or replace function is_date( test_date in varchar(20) ) return date
is
begin
  return to_date( test_date );
  exception
    when others then
      return null;
end;

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi duck,

The problem isn't that 0622 is a bad year.  It's actually quite valid.  The problem is probably that the two systems use different values for the epoch date so that 0622 is a valid year in the existing system but not in the new one.

Instead of testing for that particular date, test that the date in the string is prior to the epoch date, or call isdate() to validate the date before converting it.

Kent
0
 
thayduckProgrammer AnalystAuthor Commented:
I have no control over dates coming into this Oracle table. All I can do is process the date that is there.

How would I use isdate() in the open query Oracle statement ?
0
 
awking00Commented:
Just sounding off about one of my pet peeves. It's a shame you have to deal with this because someone on the Oracle side deemed to treat dates as something other than a date data type. Why don't they understand you can't do date math with characters? It's like trying to do basic math with Roman numerals. It can be done, but why make it harder.
0
 
thayduckProgrammer AnalystAuthor Commented:
I will try and work with this. Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.