I have a date stored in a file in a decimal (8, 0) field in format YYYYMMDD. Can I use SQL to list the records with an invalid date?
I did not find a direct way to check whether a date is valid only with SQL instructions, but there is a way using a user-defined function.
The idea is to build a procedure within a service program and make it usable by SQL statements.
In this case, I used RPGLE, but other languages can be used.
First of all, here is the source:
h debug nomain D verifydm pr 1 D date 8 0 p verifydm b export d pi 1 d date 8 0 c *iso test (de) date c if %error c return '1' c else c return '0' c endIf p verifydm e
To create the module, use CRTRPGMOD, then use CRTSRVPGM as follows:
CRTSRVPGM SRVPGM(LCP51D/VERIFYD) EXPORT(*ALL).
Finally, create the function inside SQL as follows:
create function verified (decimal (8,0)) returns char(1) external name
'LCP51D/VERIFYD(VERIFYDM)' language RPGLE
You can then use it to grab those records with an invalid date:
select * from xadevdta/g2m01p where verified (m01sdt) = '1'
MORE INFORMATION ON THIS TOPIC
The Best Web Links: tips, tutorials and more.
Ask your systems management questions--or help out your peers by answering them--in our live discussion forums.