VBA CDate doesn't match Convert(datetime,) in SQL Server 2005

Question

In Access 2003 VBA (Used Immediate Window)

? CDate(39870)

Returns: 2/26/2009

In SQL Server 2005 (SQL Server Management Studio)

SELECT CONVERT(DATETIME, 39870)

Returns: 2009-02-28 00:00:00.000

Do they always differ by 2 or is there some other date setting I'm missing?

Server Collation = SQL_Latin1_General_CP1_CI_AS

Answer 1

Different languages and systems use different numeric representations of dates.

SQL 2005 counts the number of days from 1/1/1900:

SELECT CONVERT(DATETIME, 1)
1900-01-02 00:00:00.000

Excel and Access VBA count the number of days from 12/30/1899:

? CDate(1) 
12/31/1899

Others count the number of days, seconds, or sometimes milliseconds from some other seemingly-random starting point. I think MS DOS used Dec 31st, 1990. UNIX uses 1/1/1970.

So don't try to translate them directly (numerically, that is). You'll have to translate to an actual date representation (01-03-2009) and then back in the other environment.

Edit: VBA has some funny quirks about backward compatibility and leap-year calculations. See Joel's first encounter with Bill Gates for a good read.

Answer 2

They use different starting points.

Access starts counting dates at 12/30/1899

SQL Server starts at 1/1/1900

That's why you get the two day difference.

In general you can't rely on using the same internal representation across different systems.