date validation function

ahmedwaseem2000 · #1 (**permalink**) 12-29-08, 06:44

Hi

What is the function to validate date in the select query?

Thanks,
Waseem

nick.ncs · #2 (**permalink**) 12-29-08, 10:57

Note: I have selected yyyymmdd format.....your SQL will depend on the type of date format which you use......

-checking yyyy lies between '0001' and '9999'

Quote:

SUBSTR(yyyymmdd,1,4) BETWEEN '0001' AND '9999'

as a constraint you can check values within acceptable year range e.g between 1900 and 2100

-checking mm lies between '01' and '12'

Quote:

SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'

-and the trickiest part, checking the days dd which can lie between 0 - 28,29,30,31

Quote:

INT(SUBSTR(yyyymmdd,7,2)) BETWEEN 1 AND INT(DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0, '-')||'-01',10))+ 1 MONTH - 1 DAY))

So your final code can be -

Quote:

select
case
when SUBSTR(yyyymmdd,1,4) BETWEEN '1900' AND '2100' --Making sure that year lies within acceptable range
then
case
WHEN SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'
THEN
CASE
WHEN (INT(SUBSTR(yyyymmdd,7,2)) BETWEEN 1 AND INT(DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0, '-')||'-01',10))+ 1 MONTH - 1 DAY)))
then 1
else 0
end
ELSE 0
END
ELSE 0
END CASE
FROM Tablename

where
1 = sucess
0 = failure

and yyyymmdd denotes the column name which needs to be validated

ahmedwaseem2000 · #3 (**permalink**) 12-29-08, 12:20

thanks for the code. However, I was looking for some functions that could do that?

nick.ncs · #4 (**permalink**) 12-29-08, 12:37

well don't be lazy now.... look around as how to write UDF's and apply the above logic.....

dav1mo · #5 (**permalink**) 12-29-08, 13:27

It all depends on what you mean by verify. What Nick gave you is a perfectly good example that would work well within a UDF. Then there is a more direct route as well:
SELECT DATE(:MY-DATE) FROM SYSIBM.SYSDUMMY1;
You can do this in different date formats as well, but its a quick easy way to find out if the date is good, You can do it cheaper using a set host variable as well.

Dave

stolze · #6 (**permalink**) 12-29-08, 16:00

Adding on top of what Dave said, you can do this SELECT statement inside a procedure. Extend the procedure with an exception handler that catches invalid formats and does whatever you want to do instead. Now you write a UDF that calls the procedure. For an example, search for SOFT_INT, which is a function written by Serge Rielau and it does the same - just for integers.

stolze · #7 (**permalink**) 12-29-08, 16:02

Quote:

Originally Posted by nick.ncs

Note: I have selected yyyymmdd format.....your SQL will depend on the type of date format which you use......

-checking yyyy lies between '0001' and '9999'

I guess you know that this will not work reliably because:

Code:

$ db2 "VALUES CASE WHEN '1ABC' BETWEEN '0001' AND '9999' THEN 'match' ELSE 'no match' END"

1
--------
match

  1 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech