DB2 select ... insert fails converting a leap day into a date

Question

I am attempting to do a select insert which takes a DECIMAL(8,0) representation of a date (DOB), converts it into a date, and inserts it into another table.

INSERT INTO myschema.beneficiary_info  
    (name, address, city, state, zip, dob, spouse) 
SELECT 
    jh.bname, jh.badd1, jh.bcity, jh.bstate, jh.bzip,
    CASE 
        WHEN JH.BDOB8 != '0' AND JH.BDOB8 IS NOT NULL THEN 
            DATE(
                TIMESTAMP_FORMAT(
                    CHAR(jh.bdob8) ,'YYYYMMDD'))
        ELSE null
    END,
    --jh.bdob8,
    jh.bspous
FROM TABLE(MYSCHEMA.REMOTE_TABLE()) JH    -- function pointing to a remote database

But, about 5,000 records into it (having successfully processed dates and nulls), it fails with

[Code: -181, SQL State: 22007] [SQL0181] Value in date, time, or timestamp string not valid.

I narrowed the data down to the offending row, and found this (showing results of case statement and the actual bdob8 field:

0006        bdob8     
1958-12-24  19581224  
(null)      19620229   
1965-02-07  19650207

The leap day appears to be causing the select ... insert to fail.

At this point, the case statement has already successfully navigated nulls by simply passing the null to the insert. Yet for some reason, when the date/timestamp functions barf and return a null, the whole thing breaks.

Looking for ideas how to overcome the apparent inability to handle leap days...

(Using IBMi DB2 V7R3M0 L00)

1962 was not a leap year so the date is invalid. 1960 and 1964 were leap years. — Gordon Linoff, Mar 29 '19 at 21:45
wow, that was fast ;) Thanks for the help. I completely overlooked that little tidbit. — Tim Morton, Mar 29 '19 at 21:48
In general years evenly divisible by 4 are leap years ( year % 4 = 0). There is a different rule for centuries (1800, 1900, 2000) but you have 81 years before that's a problem. — Jim Castro, Mar 29 '19 at 22:07

score 1 · Answer 1 · answered Apr 01 '19 at 14:08

1

As Gordon pointed out, 1962 isn't a leap year.

I'd suggest building your own User Defined Function (UDF) that does the date conversions...

That way you'll be in control of what's returned for invalid dates. You can also handle common numeric special values (or return null)

exmaple

00000000 --> 0001-01-01
99999999 --> 9999-12-31

There's a popular open source package of UDF's available, iDate by Alan Campin, that would make a great starting place for your own UDF.

answered Apr 01 '19 at 14:08

Charles

21,637
1
20
44

Since I can't count on the data being corrected in a timely fashion, I do need to be able to handle the errors; your answer is very much appreciated. Is it necessary to invoke an external program to handle the error? Or could it be handled in a UDF that uses a query? (I noticed that when I run a query, I just get a null, which I think would be fine to pass to the insert since I have no idea what the date was supposed to be). In the meantime, I'll continue to look through your link. I'll probably get our RPG guy involved since that part is out of my league... – Tim Morton Apr 01 '19 at 14:41
@TimMorton I think you need the UDF, there's no way to handle exceptions at the record level inside a `insert`ed set of records. – Charles Apr 01 '19 at 14:54

score 0 · Answer 2 · answered Apr 01 '19 at 18:36

Use a UDF to filter bad data

CREATE FUNCTION ASDATE (
  INPUTDATESTR VARCHAR(10) )
  RETURNS DATE
  LANGUAGE SQL
  SPECIFIC ASDATE
  NOT DETERMINISTIC
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  CONCURRENT ACCESS RESOLUTION DEFAULT
  SET OPTION  ALWBLK = *ALLREAD ,
  ALWCPYDTA = *OPTIMIZE ,
  COMMIT = *NONE ,
  DECRESULT = (31, 31, 00) ,
  DYNDFTCOL = *NO ,
  DYNUSRPRF = *USER ,
  SRTSEQ = *HEX
  BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL ;
RETURN DATE ( TIMESTAMP_FORMAT ( CHAR ( INPUTDATESTR ) , 'YYYYMMDD' ) ) ;
END  ;

It is accessed like this:

INSERT INTO myschema.beneficiary_info  
    (name, address, city, state, zip, dob, spouse) 
SELECT 
    jh.bname, jh.badd1, jh.bcity, jh.bstate, jh.bzip,
    CASE WHEN ASDATE(jh.bdob8) IS NOT NULL THEN ASDATE(jh.bdob8) ELSE NULL END
    --jh.bdob8,
    jh.bspous
FROM TABLE(MYSCHEMA.REMOTE_TABLE()) JH    -- function pointing to a remote database

Many thanks to Charles' pointing me in the right direction, and https://stackoverflow.com/a/39683452/2129574

The options in the UDF are the defaults that IBM Navigator for i filled in; any comments about improvements to the function are welcomed.

DB2 select ... insert fails converting a leap day into a date

2 Answers2

Use a UDF to filter bad data