• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Disrupt Without Disruption

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    Learn How

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    IBM Brings Rack-Mounted UPS to iSeries The eServer i5, i5/OS V5R3 Continue the AS/400 Transformation

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle