Converting Unconvertible Dates Using SQL

Published: April 26, 2006

by 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.