Exporting DB2/400 Dates to Excel

Published: January 10, 2007

by 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