db2 - Valid date verification in SQL

Question

I have a column that holds LossDate in the format of (yyyymmdd). I need to verify that if the month ends in 30 days, then no loss was reported with a loss date of 31. I have millions of records. Help will be highly appreciated.

LossDate
--------
20120128
20150520
20180631

Query should return the last invalid record because 200806 ends in 30 days.

Answer 1

You could create an ISDATE a function such below and then use it in a query such as SELECT * FROM TABLE WHERE DB_IS_DATE(LOSSDATE) = 0 to find all your invalid dates

CREATE OR REPLACE FUNCTION IS_DATE(YYYYMMDD INTEGER)
RETURNS SMALLINT
LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN
  CASE
    WHEN YYYYMMDD/10000 BETWEEN 1 AND 9999
    AND(    ( MOD(YYYYMMDD/100, 100) IN (1,3,5,7,8,10,12) AND MOD(YYYYMMDD,100) BETWEEN 1 AND 31 )  -- Jan,Mar,May,Jul,Aug,Oct,Dec have 31 days
         OR ( MOD(YYYYMMDD/100, 100) IN (4,6,9,11)        AND MOD(YYYYMMDD,100) BETWEEN 1 AND 30 )  -- Apr,Jun,Sep,Nov             have 30 days
         OR ( MOD(YYYYMMDD/100, 100) = 2                  AND MOD(YYYYMMDD,100) BETWEEN 1 AND 28 )  -- Feb has 28 days
         OR ( MOD(YYYYMMDD/100, 100) = 2                  AND MOD(YYYYMMDD,100) BETWEEN 1 AND 29    --   unless is a leap year. i.e.
              AND ( ( MOD(YYYYMMDD/10000,4) = 0 AND MOD(YYYYMMDD/10000,100) <> 0)                   --   year is divisable by 4 but not 100
                OR  MOD(YYYYMMDD/10000,400) = 0 )                                                   --     or year is divisable by 400
            )       
         )
    THEN 1
    ELSE 0
  END

If you are using Db2 Warehouse you could return a BOOLEAN from the function. E.g.

CREATE OR REPLACE FUNCTION IS_DATE(YYYYMMDD INTEGER)
RETURNS BOOLEAN
LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN
        YYYYMMDD/10000 BETWEEN 1 AND 9999
    AND(    ( MOD(YYYYMMDD/100, 100) IN (1,3,5,7,8,10,12) AND MOD(YYYYMMDD,100) BETWEEN 1 AND 31 )  -- Jan,Mar,May,Jul,Aug,Oct,Dec have 31 days
         OR ( MOD(YYYYMMDD/100, 100) IN (4,6,9,11)        AND MOD(YYYYMMDD,100) BETWEEN 1 AND 30 )  -- Apr,Jun,Sep,Nov             have 30 days
         OR ( MOD(YYYYMMDD/100, 100) = 2                  AND MOD(YYYYMMDD,100) BETWEEN 1 AND 28 )  -- Feb has 28 days
         OR ( MOD(YYYYMMDD/100, 100) = 2                  AND MOD(YYYYMMDD,100) BETWEEN 1 AND 29    --   unless is a leap year. i.e.
              AND ( ( MOD(YYYYMMDD/10000,4) = 0 AND MOD(YYYYMMDD/10000,100) <> 0)                   --   year is divisable by 4 but not 100
                OR  MOD(YYYYMMDD/10000,400) = 0 )                                                   --     or year is divisable by 400
            )       
         )

The function can be used to convert the input to a DATE if it is a valid date. E.g.

SELECT i, IS_DATE(i) AS IS_DATE
,      CASE WHEN IS_DATE(i) = 1 THEN DATE(TO_DATE(DIGITS(DECIMAL(i,8,0)),'YYYYMMDD')) END AS DATE
FROM TABLE(VALUES(-999),(0),(1),(00010101),(99991231),(20180101),(20180228),(20160229),(20180229),(20000229),(19000229)) as D(i)

which returns

 I        IS_DATE DATE
 -------- ------- ----------
     -999       0 NULL
        0       0 NULL
        1       0 NULL
    10101       1 0001-01-01
 99991231       1 9999-12-31
 20180101       1 2018-01-01
 20180228       1 2018-02-28
 20160229       1 2016-02-29
 20180229       0 NULL
 20000229       1 2000-02-29
 19000229       0 NULL

The function will also accept strings of "YYYYMMDD" format. Db2 will cast the string to an integer for you. If you have a string of e.g. "YYYY-MM-DD" format, you could create a character version of the function that calls the above function such as

CREATE OR REPLACE FUNCTION IS_DATE(YYYYMMDD VARCHAR(10))
RETURNS INTEGER
LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN
   CASE WHEN REGEXP_LIKE(YYYYMMDD,'[0-9]{4}?-[0-9]{2}?-[0-9]{2}?') = 1 
        THEN IS_DATE(INTEGER(REPLACE(YYYYMMDD,'-',''))) ELSE 0 END

Another option is to create a UDF that uses in-built Db2 functions, but catches any error that they might generate. E.g.

CREATE OR REPLACE FUNCTION IS_DATE(input VARCHAR(32), format VARCHAR(32)) 
RETURNS INTEGER
LANGUAGE SQL CONTAINS SQL DETERMINISTIC ALLOW PARALLEL NO EXTERNAL ACTION
BEGIN 
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
    RETURN 0;
  RETURN CASE WHEN TO_DATE(input, format) >= '0001-01-01' THEN 1 ELSE 0 END;
END
@