Problem with date between Excel and SQLServer

[lucianot]

I have an Excel function that runs an SQL Server function that returns a Datetime field. The function is getting the correct record, but when I convert it to Excel Date format it seems off by 2 days (the date on Excel is 2 days lower than the correct SQL value). Of course I can simply add 2 days, but I'm wondering why this is happening in the first place? Is there any way to sync both?

 

[DonkeyOte]

Not experienced this myself but it seems base date for SQL Server is midnight Jan 1 1900... so noon Jan 4 1900 appears in SQL Server to be 3.5 whereas in XL obviously this is 4.5 -- that's 1 of the 2... does that make sense ? Day 2 in SQL is 1 day higher than day 2 in Excel.

http://www.devarticles.com/c/a/SQL-Server/Date-and-Time-Values-Using-SQL-Server-2000/1/

 

[xenou]

That's a very interesting SQL article! If I understand it correctly, 0.00 is midnight on Jan 1, 1900 - or in other words, the first moment of Jan 1. I believe this is also what Excel does. Or otherwisely once more, 0.001 is about a minute into Jan 1 in both systems.

One thing that might be going on is that you are just making a mistaken assumption here - how do you know the data is wrong? When you "see" this in SQL server what do you "see" (so that you thereby know Excel is wrong)?

If you have control over your function,
1) you may find it useful to first be extremely careful about time values (they can really make havoc - i.e., "<= 9/30/08" and "< 10/01/08" may be very different).
2) you may find it useful to convert the datetime to a string and import that to Excel instead of the underlying numeric. Generally I don't actually advise this but it might be a way to "cut through the gordian knot" - assuming you are able to pull good dates in SQL you'll be sure to have the same dates in Excel (which can be re-converted to numeric dates).

This certainly begs a mystery to be solved.

 

[DonkeyOte]

I'm not sure either - it would seem very odd for 2 MS products to have different base dates but this:

The fractional portion represents the fraction of a day that’s passed since midnight. For example, the date/time value representing noon on January 4, 1900 is stored as 3.5. In this case, 3 represents three full days since the base date and 0.5 represents one half of a day between midnight and noon. To see this, submit the following query:

SELECT CAST(CAST('1900-01-04 12:00' AS datetime) AS float)

The same datetime value in Excel would generate 4.5 -- a difference of 1 day.

 

[xenou]

I see. Yes, I stand corrected. So its the difference between being one-based and zero-based. Or more generally, in Excel a 0 integer part will be interpreted as Jan 0, 1900 (whatever that means) - in essence, meaning that a zero is a non-date and only the decimal/time portion is relevant. In SQL Server, a 0 integer portion is day 1.