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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.