Microsoft Seed date <> AS/400 ?

[WildHare]

I was playing with some stuff today at work, and noticed an oddity - we use several different AS/400 and iSeries based databases for our corporate production applications - mostly supplied by third-party vendors who deliver hotel and casino systems.

For most of these systems, date values are displayed in a 'hundred year format', what we would call the serial date value measured from a particular starting date. In the case of our systems, and Microsoft's programs, I believe that date is January 1, 1900, which equates to "1".

However, I noticed that our AS/400 systems display today (August 20, 2003) as 37852, where as if you type today's date in to an Excel cell and convert it to a number, you get 37853 - one day later.

Is this due to MS adhering to the "The year 2000 was a LEAP YEAR" formula, and the AS/400 guys NOT adhering to it? It's not a big deal, but since we transfer an awful lot of date-related data into back and forth between the mini and our PCs, I thought this was noteworthy.

Any thoughts from the crowd?

Thanks

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[Zathras]

Not 2000, but 1900 is probably the culprit:

The AS/400 used here generally stores dates as CYYMMDD where C is a century code (0 = 20th century, 1 = 21st century, etc.). Conversion to an Excel-style date number would be done by software.

Here's the rub: When Lotus first brought out 1-2-3, they used the date number scheme that Excel uses today. Unfortunately, the programmers made the blunder of thinking that 1900 was a leap year, which of course it is not. Nevertheless, for consistency sake, any date numbering scheme created today is forced to make the same error or it will be off by one when compared with Excel dates.

My guess is that your vendor is counting the exact number of days from 12/31/1899 so that 1 = 1/1/1900 and 59 = 2/28/1900. However they probably say 60 = 3/1/1900 which is technically correct, but inconsistent with Excel, which says that 60 = 2/29/1900 even though there is no such date.

 

[WildHare]

Aha! So basically, this is Mitch Kapor's fault? LOL..

Thanks for the explanation - it sounds logical.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[TonyJollans]

Very interesting Zathras,

I hadn't realised that Excel did that on Worksheets. It also, I see, displays zero as 0 Jan 1900, and can't cope with negatives. With typical Micro$oft consistency, VBA produces valid dates based on 30 Dec 1899 (as documented).

I can't speak for AS/400s but the convention of basing dates on 1 Jan 1900 goes back a long way and I believe the ICL 1900 series was named for it. Although we all make use of internal formats (as many threads here confirm) it is not safe to assume any cross-platform (or even cross-application) consistency.

Enjoy,
Tony