• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Exporting DB2/400 Dates to Excel

    January 10, 2007 Jeff Haddix

    In order for Microsoft‘s Excel spreadsheet to recognize the set of values for a column as dates, those values must be stored as a number of days. For example, to export the date December 26, 2006, to an Excel spreadsheet, you should export the number 37,066, not a date value like 12/26/2006 or 2006-12-26. However, due to an acknowledged defect in Excel, be aware of the following.

    Even though Excel claims that 01/01/1900 equates to Day 1, for most purposes it is necessary to actually use 12/31/1899 as Day 1. Otherwise, a one-day discrepancy will occur when converting IBM DB2 dates (where Day 1 is 01/01/0001) after 02/28/1900 into Excel.

    The reason is that Excel does not accurately apply the leap year calculation, while IBM does. The frequently ignored part of the calculation is that century years (1700, 1800, 1900, and so forth) are not leap years, even though they are multiples of four. The exception to that rule is that every 400 years (1200, 1600, 2000, etc.) the century year is a leap year.

    As such, February 1900 only had 28 days. However Excel erroneously includes a 29th day, thus creating a one-day offset for all subsequent date conversions.

    In the simple test below, a short series of integers was keyed and then those cells were formatted as dates. Notice that Excel improperly converts day 60 to 2/29/1900, which is a non-existent date, thus creating a one-day offset for all subsequent integer date conversions.

    The following SQL query demonstrates how to derive Excel compatible dates from columns defined as either legacy packed-decimal date values or with a DATE datatype.

    select   DECIMAL_DATE,                                           
               days( date( substr(digits(DECIMAL_DATE),1,4) || '-' ||
                           substr(digits(DECIMAL_DATE),5,2) || '-' ||
                           substr(digits(DECIMAL_DATE),7,4) ) )      
             - days( date('1899-12-30') )  as DAYS_FROM_DECIMAL_DATE,
             ISO_DATE,                                               
               days( ISO_DATE)                                       
             - days( date('1899-12-30') )  as DAYS_FROM_ISO_DATE     
    from     DATETABLE 
    order by 1 desc
    

    This will return a result set similar to the following.

    DECIMAL_DATE  DAYS_FROM_DECIMAL_DATE  ISO_DATE    DAYS_FROM_ISO_DATE
     2006/12/26              39,077       2006-12-26            39,077  
     2006/12/17              39,068       2006-12-17            39,068  
     2006/12/01              39,052       2006-12-01            39,052  
     2006/02/01              38,749       2006-02-01            38,749  
     2006/01/01              38,718       2006-01-01            38,718  
     2003/05/11              37,752       2003-05-11            37,752  
     2003/05/01              37,742       2003-05-01            37,742  
     2003/04/22              37,733       2003-04-22            37,733  
     2003/04/15              37,726       2003-04-15            37,726  
     2003/04/01              37,712       2003-04-01            37,712  
     2003/03/01              37,681       2003-03-01            37,681  
     2003/02/08              37,660       2003-02-08            37,660  
     2003/01/15              37,636       2003-01-15            37,636  
     2003/01/06              37,627       2003-01-06            37,627  
     2003/01/02              37,623       2003-01-02            37,623  
     2003/01/01              37,622       2003-01-01            37,622  
     2002/12/15              37,605       2002-12-15            37,605  
     2002/12/10              37,600       2002-12-10            37,600  
    

    Happy date data exporting, everyone.

    Jeff Haddix is the software architect at Alliance Data, which provides application software for utility companies. He can be reached at jeff_haddix@yahoo.com.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the Annual 2007 Conference & Expo, April 29 - May 3, in Anaheim, California
    Bytware:  StandGuard Network Security 3.0, the next generation of System i security
    Asymex:  Control spool files automatically with SpooliT - intelligent iSeries spool file management

    Global Signs Terillium to Resell Spreadsheet Server Original Adds Some Manual Features to Testing Suite

    Leave a Reply Cancel reply

Volume 7, Number 1 -- January 10, 2007
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL
CYBRA

Table of Contents

  • Exporting DB2/400 Dates to Excel
  • Resetting Your QSECOFR Service Tools Password
  • Admin Alert: Combating Cross-Server Failures for the i5 Manager

Content archive

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

Recent Posts

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.