Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
capooti
Starting Member
4 Posts |
 Posted - 2002-05-10 : 07:13:38
|
| 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:002002-05-12 00:00:00If 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:002002-05-10 00:00:00How 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 |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-05-10 : 07:32:50
|
| Am I missing something?1900-01-00 is an invalid date.1900-01-01 is January 1st 1900--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-05-10 : 08:38:03
|
| Excel for Windows, Excel for the Macintosh (in the past, at least) and Access all have different date origins, so this is utter nonsense.You might, looking at Excel and Access, be inclined to think they were the same, but you'd be wrong. Ignoring what 19000-01-00 might be intended to mean, we'll try the day after:SELECT CInt(#01/01/1900#); in Access yields 2DATEVALUE("01/01/1900") in Excel yields 1The reason for this is that Excel remains bug-compatible with Lotus 1-2-3, the programmers of which were so unfamiliar with the Gregorian calendar that they thought 1900 was a leap-year. Access retains compatibility with Excel for dates from 1900-03-01 onwards. The general statement that 1899-01-01 is the "right" day 0 is meaningless. |
|
|
|
|
|
|
AMAZING BUG WITH DATE ????