Date Check in DB2 UDB urgent

[Suresh]

Hi All,

I have varchar column which stores data in 8 char format 'yyyymmdd'. i
want to transfer data in date columns but before that i want to check
data is proper date or not.

like in sql server i have isdate function. i didnt seen any function
like that in DB2 UDB. can any one have UDF return to check date in DB2.
please suggest any other way,

thanks
Suresh

[Tonkuma]

How about this?

CREATE FUNCTION ISDATE(yyyymmdd VARCHAR(8))
RETURNS INTEGER
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN TRANSLATE(yyyymmdd, '*', ' 0123456789') = '' THEN
CASE
WHEN SUBSTR(yyyymmdd,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'
AND INT(SUBSTR(yyyymmdd,7,2))
<=
DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0,'-')||'-01',10)) + 1
MONTH - 1 DAY)
THEN 1
ELSE 0
END
ELSE 0
END;

[Tonkuma]

Need (at lest) one correction)
"dd" should be checked lower limit(i.e. '01')

CREATE FUNCTION ISDATE(yyyymmdd VARCHAR(8))
RETURNS INTEGER
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN TRANSLATE(yyyymmdd, '*', ' 0123456789') = '' THEN
CASE
WHEN SUBSTR(yyyymmdd,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'
AND INT(SUBSTR(yyyymmdd,7,2)) BETWEEN
1 AND
DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0,'-')||'-01',10))
+ 1 MONTH - 1 DAY)
THEN 1
ELSE 0
END
ELSE 0
END;