Q
Problem solve Get help with specific problems with your technologies, process and projects.

Check whether a date is valid

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.


Dig Deeper on iSeries ILE programming

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close