• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Converting Unconvertible Dates Using SQL

    April 26, 2006 Ted Holt

    As much as I like SQL, I realize that it has its shortcomings. Fortunately, SQL’s shortcomings are not hard to work around. In the few paragraphs that follow, I give you a quick and easy way to get around the problem of converting invalid character data to the date data type.

    When I first began learning to program, System/34 RPG II was the most common programming language in my area. The S/34 had no database engine and RPG II had no date data type, so we stored dates in numeric and/or alpha fields. (Nowadays, dates are still stored in numeric and/or alpha fields in many shops.)

    One of the drawbacks of storing dates in numeric and alpha fields is that the system does not ensure that the dates are valid. If someone wants to store an April 31 date in a numeric field, the system won’t complain. Many of us have taken advantage of this liberty, often using invalid dates to indicate special values. For example, an expiration date of all nines might mean that something never expires.

    Now, suppose a file contains two dates in numeric or alpha fields, and we wish to calculate the number of days between the two using SQL. The usual way to handle this sort of thing is to convert both dates to the data type and give the converted values to SQL’s date-handling features. But if you tell SQL to convert an invalid date to the date data type, SQL complains loudly and hands you an error message.

    The SQL shortcoming is this: the date-handling functions in SQL assume that a character string argument contains a valid date in a proper date format. Some wise person told me eons ago that when you “assume,” it makes an “ass” of “u” and “me,” so I realized that I was going to have to make SQL quit assuming. I did so by writing a simple function, which I call IsValidDate.

    create function qgpl/IsValidDate            
       (InValue varchar(10))                    
       returns char (1)                         
    language sql                                
    returns null on null input                  
    begin                                       
       declare CharDate date;                   
       declare exit handler for SQLState '22007'
          return '0';                           
       set CharDate = Date(InValue);            
       return '1';                              
    end                                         
    

    IsValidDate accepts an argument of ten characters or less, which is supposed to contain a date in an acceptable date format. I have used IsValidDate successfully against character strings in MM/DD/YY and YYYY-MM-DD formats. If the argument contains a valid date, the function returns a single character 1 (one). Function IsValidDate returns a single 0 (zero) if the date is invalid. And if the argument is null, the function returns null.

    The following short example copies data from one database table (file) to another. The file that is being copied contains an eight-byte character date (SomeDate) in MMDDYYYY format. The file to receive the data has a field of the date data type. Any invalid dates are to be converted to January 1, 1995.

    insert into AnotherFile
    select RecID,
       case
          IsValidDate(substr(somedate,1,2)||'/'||
                      substr(somedate,3,2)||'/'||
                      substr(somedate,7,2))
             when '1'
                then Date(substr(somedate,1,2)||'/'||
                          substr(somedate,3,2)||'/'||
                          substr(somedate,7,2))
                else Date('01/01/95')
             end 
           from SomeFile
    

    The CASE passes the alpha date data, in MM/DD/YY format, to the IsValidDate function, which returns a 1 or something else. If IsValidDate returns a 1, the alpha data is converted to a date and written to AnotherFile. Otherwise, SQL converts the string 01/01/95 to a date value and writes it to the output file.

    If you run up against SQL’s unforgiving spirit, I hope the IsValidDate function will be of value to you.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Maximum Availability:  Secure, cost-effective, real-time iSeries replication software solutions
    SoftLanding Systems:  TurnOver Change Management for a more productive WDSc environment
    COMMON:  Join us at the Fall 2006 conference, September 17-21, in Miami Beach, Florida

    Digital TV Meets Portal Technology Migration RPG: Another Option for RPG II Shops

    Leave a Reply Cancel reply

Volume 6, Number 17 -- April 26, 2006
THIS ISSUE SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
Twin Data

Table of Contents

  • Converting Unconvertible Dates Using SQL
  • Auditing of Sensitive Users and Objects
  • Allowing User Profiles Without Passwords to Sign On

Content archive

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

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

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