AMAZING BUG WITH DATE??? 2

[capooti]

Guys, I think I discovered an incredible bug.
I want to convert a numeric value rappresenting the number of days from 1-gen-1900 in a smalldatetime.
I do this:
select cast(0 as smalldatetime)
select cast(37386 as smalldatetime)
I get:
1900-01-01 00:00:00
2002-05-12 00:00:00

If you do the same kind of operation from ANY software (ie: Excel or Access), you instead get the right values:
1900-01-00 00:00:00
2002-05-10 00:00:00

How is it possible that the SQL conversion start from the wrong value (1900-01-01 instead of 1900-01-00) and ends loosing 1 day?????

It seems that Bill Gates striked back...

I will be very grate with everyone will give me his opinion about it

 

[SQLBill]

A guess on my part would be that since 1900-01-00 isn't a real date (there can't be a day '00'), the real date 1900-01-01 (1 January 1900) is being used.

So, in SQL Server you haven't 'lost' a day. It's just that in the other programs you have gained a day that doesn't exist in real life.

-SQLBill

 

[bperry]

That's quite interesting. I hope someone tells us why!

I'm gussing here that it must have something to do with time (?). A datetime field consists of a 4-byte integer for the date, plus a 4-byte integer for the time.

 

[SQLBill]

Okay...I put my foot in my mouth...

Here's what I have found so far....

EXCEL uses 1900-01-01 as it's starting date
SQL Server uses 1900-01-01 as it's starting date.

BUT

EXCEL assigns 1900-01-01 as 1 (making 0 = 1900-01-00, which is the same as making 1900-01-00 the start date)
SQL Server assigns 1900-01-01 as 0

So yes there is a day difference due to the number assigned to 1900-01-01. But the other bug you didn't mention, but it's shown in your example, is that there is a TWO day difference in the second example:

SQL Server:
select cast(37386 as smalldatetime)
2002-05-12 00:00:00

EXCEL
2002-05-10 00:00:00

Why? I haven't figured that out yet...partly it's caused by the different starting number (0 for SQL and 1 for EXCEL). But the second lost day might be due to 2000 being a leap year (years that end in ..00 are not leap years unless they are divisible by 400).

-SQLBill

 

[tlbroadbent]

The VB environment of Access, Excel etc. use a different starting point for dates than SQL Server. It is simply a matter of design. You can call it a bug if you want but it isn't an issue unless you try to interchange dates based on the serial number. To avoid date problems between different environments, use strings for dates rather than the number. The numbers are only valid in a given environment.

By the way, VB related products handle dates incorrectly. SQL Server corrects a common problem with date functions.

SQL Server's starting date is 1/1/1753. VB uses 1/1/100. Why is SQL Server correct and VB wrong? The Gregorian calendar, upon which these date functions and values are based, was changed in 1752 and 11 days were deleted. In October 1752 the calendar skipped from the 4th to the 15th in order to bring the calendar in synch with the seasons. This change was not adopted universally at the same time. Thus correct dates and serial numbers prior to Oct. 15, 1752 depend on locale.

The approach taken in SQL Server is to simply avoid the problem by rejecting all dates prior to 1/1/1753. While not helpful when dealing with historical dates prior to 1753, it does insure that date calculations are correct within the domain of valid dates. This is not true in products that use an earlier start date.

If you want to use the same date numbers in Excel, Access, SQL and other products, consider Julian based dates and performing your own calculations. In my work, it's not worth the effort.

http://aa.usno.navy.mil/data/docs/JulianDate.html

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[capooti]

Hey, thanks for your interest!!!

Sorry, Terry
but I am not agree with you.
When you use string instead than number, you go in a lot of troubles, expecially if you need to use funcitons for date (ie. Day, Year, Month). If you need to manage tables from different countries you would get a lot of troubles in decoding the date, because of international setting. (Ie. one of the USA international setting for date is ie. "05-10-2002", instead for the same date with Italian settings you get "10-05-2002". So I think the numeric format for date is really the best, because is unique for all countries.
But unluckly 2 days were lost from SQL Server... who knows whyyyy????

And anyway for my project I get huge files to load into SQL with numeric format for date, so I will bypass the bug putting -2 to all date (ie: day(38345 -2))

Anyway thank you very much for your always precious help

 

[karluk]

Actually the bug is in Excel, not SQL Server. I get the following results in Excel:

DATEVALUE("02/28/1900&quot = 59
DATEVALUE("02/29/1900&quot = 60
DATEVALUE("03/01/1900&quot = 61

That means that Excel is treating 1900 as a leap year even though it's not. SQL Server does it right:

select cast(58 as smalldatetime) = 1900-02-28 00:00:00
select cast(59 as smalldatetime) = 1900-03-01 00:00:00

As SQLBill pointed out, century years are not leap years in the Gregorian calendar unless they are evenly divisible by 400. Thus 2000 was a leap year, but 1900 wasn't.

The other discrepancy has already been noted: the products pick a slightly different interpretation of day 0.

 

[VogonPoet]

What happens when you import date data from Access/SQL? Does SQL shift the date by 1 to 2 days or does the ODBC/DTS/Other driver take care of the different date methodologies?

Michael

 

[capooti]

congrats karluk

it is not a sql server bug.
at this point the source of my data where processed with a software using not Gregorian date

thank you all!

 

[tlbroadbent]

capooti,

Disagreement is allowed. I'm glad you found a solution that works for you.

I appreciate the additional insights offered by SQLBill and Karluk.

When one learns how to internationalize dates, using strings isn't a problem. Yes, the local settings may differ but there are standard formats that work across many platforms and locales. For example, Access, Excel and SQL will interpret '2002-05-02' (yyyy-mm-dd) as the same date regardless of locale.

I prefer to work with strings because I understand them. I can look at an import or export file and recognize dates. I can interchange date easily between SQL Server and the other products mentioned with no difficulty. I can send the file to Germany, India, England or Brazil and it can be utilized. It not so easy using serial numbers which we've seen are problematic regardless of platform or locale.

May I recommend that you read an aricle I wrote about dates. If you prefer to skip my article, I've included some good links about using dates in SQL Server at the bottom of the article.

Handling Dates in SQL Server

http://tlbroadbent.home.attbi.com/sql_server_datetime.htm

I need to update the article and add some more information. If anyone has other recommendations or corrections, please let me know. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[capooti]

Terry!!!

I went to your site, your tech article show me that you have a lot of experience, but I already knew it from your answers. Also the picture of you show me that you have a lot of experience (joking!!!) so I will blindly follow your reccomandations, cause in front of you I am just an unexperienced kid. This mean that in the future I will think to put in string every kind of date I will!!!

ciao

rol

 

[tlbroadbent]

I wouldn't dare suggest that you "blindly" follow me or anyone else. That idea definitely gets a from me.

You have found a solution that works. That's indicates your ability. I have a lot of experience but that doen't mean I'm not open to new ideas, methods and techniques. I have preferences and opinions. My preferences may not always be the best approach. Take what I post here, use what you want, and discard the trash. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[fluteplr]

In case any one cares, the history on this 'Bug' is that the original version of of Lotus 123 got it wrong. I would not be suprised if the Viscalc people had it wrong also.

They assumed 1900 was a leap year, it was not.

Most spreadsheet programs written after continued the bug to stay compatible with Lotus.

I first ran into this about 11-12 years ago, converting Lotus data to Foxpro. The Foxpro people were either smarter or dumber depending on your point of view and did not continue the bug.