Valid date verification in SQL

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.

回答1:

For your specific question, the following will return bad rows:

select * 
from table 
where substr(lossdate,5,2) in ('04','06','09','11')
    and substr(lossdate,7,2) > '30'

Depending on your input interface, you may need to repeat that for months with 31 days that show values greater than 31.

select * 
from table 
where substr(lossdate,5,2) in ('01','03','07','08','10','12')  
    and substr(lossdate,7,2) > '31'

Feb is a little trickier. Start incrementally, identifying all February entries with more than 29 days, you can get rid of/fix those immediately.

select * 
from table 
where substr(lossdate,5,2) = '02' 
    and substr(lossdate,7,2) > '29'

Then you need to repeat this for the remaining rows with Feb dates greater than 28 and aren't a leap year. In the last 100 years, any year divisible by 4 is a leap year, so you can identify the remainder with this (assuming you corrected/deleted the bad Feb entries you already found):

select * 
from table 
where substr(lossdate,5,2) = '02' 
    and mod(substr(lossdate,1,4),4)) <> 0 
    and substr(lossdate,7,2) > '28'

回答2:

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

;