Find Database Records with Invalid Dates

April 28, 2004 Hey, Ted

We store dates in YYYYMMDD format in packed decimal fields in our database. This served us well when we wrote all our applications in green-screen RPG. Now that we’re using other technologies, invalid dates (like all zeros, all nines, or April 31) cause us problems. Is there a way in SQL to test the validity of a date? Something similar to RPG’s TEST op code with the D extender would be great.

–Kenny

I don’t know of an SQL function that will tell you whether a numeric field contains a valid date, so I wrote one.

Use the method of your choice (interactive SQL, iSeries Navigator, Howard Arner’s SQLThing) to run the following SQL code on your iSeries machine.

create function IsValidDate (parmDate decimal(8))
 returns integer                                 
 language sql                                    
 deterministic                                   
 contains sql                                    
 not fenced                                      
 called on null input                            
 no external action                              
 set option datfmt = *iso                             
 begin                                                
    declare chDate8 char (8);                          
    declare chDate10 char (10);                        
    declare dDate date;                                
    declare bValid integer;                            
    declare continue handler for sqlexception          
       set bValid = -1;                                
    set bValid = 0;                                    
    set chDate8 = digits(parmDate);                    
    set chDate10 = substr(chdate8,1,4) || '-' ||       
                   substr(chdate8,5,2) || '-' ||       
                   substr(chdate8,7,2);                
    set dDate = chDate10;                              
    return bValid;                                     
 end                                                  
 

This function returns zero if the date is valid and negative one if the date is invalid.

The following example uses the IsValidDate function to find records with an invalid JEDATE.

SELECT *
  FROM gljnl
  WHERE IsValidDate(jedate) < 0                                

–Ted