• 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
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    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