By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

Date Check in DB2 UDB urgent

P: n/a
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

Aug 30 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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;

Aug 30 '06 #2

P: n/a
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;

Aug 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.