Dealing with Divided Date Fields

February 11, 2004 Hey, Ted

Some of our ancient database files have separate fields for year, month, and day parts of a date. Combining separate fields to form single date fields is manageable but messy. Building logical files to combine the fields is not always practical, although we have done some of that. When digging around on Google one day, I accidentally discovered an SQL function that is well-suited for divided date fields.

The function I discovered is called DateSerial. It is a part of various software products, including Microsoft Access and various versions of Visual Basic. It requires three arguments: year, month, and day. My reading leads me to believe that some implementations of DateSerial require that the three arguments be valid (that is, the month must be between 1 and 12), but it appears that most implementations allow any integer value for month and day, which lets me do some interesting and powerful things.

I decided to write a DateSerial function for DB2/400. Here it is.

create function MYLIB/DateSerial                      
   (vYear dec (4), vMonth dec (2), vDay dec (2))
   returns date                                 
   language sql                                 
   contains sql                                 
   deterministic                                
   not fenced                                   
   returns null on null input                   
   no external action                           
   allow parallel                               
   begin                                   
      declare f_output date;               
      declare f_test integer;              
      declare continue handler             
         for sqlexception set f_test = 1;  
      set f_test = 0;                      
      set f_output =                       
         date(digits(vyear) || '-01-01') + 
         (vmonth - 1) months +             
         (vday - 1) days;                  
      if f_test = 0                        
         then return f_output;             
         else return null;                 
      end if;                              
    end                                     

There are several ways to create the function. You can run it in an interactive SQL/400 session. You can run it from a PC application, such as iSeries Navigator (or whatever they’re calling it this week), or Howard Arner’s SQLThing application. Or you can put the code into a source member and use the Run SQL Statements (RUNSQLSTM) command. Be sure to change MYLIB in the first line to a library of your choosing.

To illustrate some of the things you can do with DateSerial, here’s an example that uses a file containing fields called YEAR, MONTH, and DAY.

select
       dateserial(year,month,day) as current,        
       dateserial(year-1,1,1) as BeginPriorYear,     
       dateserial(year-1,12,31) as EndPriorYear,     
       dateserial(year,1,1) as BeginCurrYear,        
       dateserial(year,12,31) as EndCurrYear,        
       dateserial(year,month,0) as EndPriorMonth,    
       dateserial(year,month+1,0) as EndCurrMonth,   
       dateserial(year,month+1,1) as BeginNextMonth, 
       dateserial(year,month+2,0) as EndNextMonth    
from somefile

The DateSerial functions retrieve the following values in this order:

• the date in the record
• first day of the prior year
• last day of the prior year
• first day of the current year
• last day of the current year
• last day of the prior month
• last day of the current month
• first day of the next month
• last day of the next month

Use your favorite search engine to search the World Wide Web for more ways to put DateSerial to work.

–Cletus the Codeslinger