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.
|
