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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    AmNet Ditches Intel Server Farm for iSeries Scalability IBM to iSeries Resellers: Learn New Skills or Be Left Behind

    Leave a Reply Cancel reply

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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