Date Check in DB2 UDB urgent

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suresh - 30 Aug 2006 08:33 GMT

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

Reply to this Message

Tonkuma - 30 Aug 2006 10:36 GMT

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;

Reply to this Message

Tonkuma - 30 Aug 2006 14:29 GMT

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;

Reply to this Message