CODEWITHSUNDEEP
sqlsql-servert-sqldatems-access
Cameron Castillo
Cameron Castillo

Reputation: 2832

Access and SQL Server calculate date differently

We have some generic tool that, among others, can save and display dates. It will save it as a number, for example 41247.

If I convert that back to a date in Access, I get 2012/12/04 - which is correct.
[using Format(41247;"General Date") or Format(Format("41247", "Short Date"), "Short Date")]

If I convert that number back to a date in SQL Server I get 2012/12/06 - no correct. I'm using CONVERT(datetime, CONVERT(real, 41247))

Why the difference, and what can I use in SQL to fix it?

Upvotes: 3

Views: 1746

Answers (2)

arunlalam
arunlalam

Reputation: 1838

So I had this issue when I was importing data from an excel file.

There are two reasons you have 2 days difference

Reason 1 In your SQL Server Jan 1, 1900 is Day 0, while in access it is Day 1.

(I haven't used Access but if you enter the date 1900-01-01 in Excel and Format the Cell as a number you ll get 1).

Reason 2 1900 was NOT a leap year. SQL Server knows it but Access doesn't. It thinks Feb 29, 1900 existed.

Run this in your SQL Server

SELECT DATEDIFF(dd, 0, '1900-01-01')
SELECT DATEDIFF(dd, 0, '1900-02-28')
SELECT DATEDIFF(dd, 0, '1900-03-01')

The output will be

0
58 
59

But when you try to run

SELECT DATEDIFF(dd, 0, '1900-02-29')

You ll get an error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Due to these 2 reasons you get 2 days more in your Access.

So for 2012/12/04 Access returns 41247, while SQL Server will give you 41245.

Hope it helped.

EDIT

Have a look at Eric Lippert's comments where he has mentioned 2 really interesting blog posts by him and Joel Spolsky.

Upvotes: 6

Gord Thompson
Gord Thompson

Reputation: 123584

There is absolutely no guarantee that two different database engines are going to store dates using the same internal numeric format. Therefore it is not reasonable to expect to...

  • take an Access Date/Time value,

  • convert it to its Access numeric equivalent, then

  • convert that number to a SQL Server datetime value

...and be guaranteed that the dates will match.

If you need to pass datetime values between database environments you'll need to pass them in a format that is common to both, such as an unambiguous character string. (If I was faced with that issue I'd be inclined to try something like the date/time format that XML uses.)

Edit

With regard to the very informative answer from @roughnex, while it may be extremely tempting to just subtract 2 from the Access date number to get the SQL Server date number (unless it's <=58, in which case you just subtract 1), that is not really such a good idea. For an explanation as to why, see the following blog entry

Bug Psychology - Eric Lippert

Shortcuts like that can lead to seemingly bizarre behaviour, which can greatly increase the wear and tear on developers (and users, too) later on.

Upvotes: 2

Related Questions