Conversion of INTEGER to DATETIME differs to VB6

Question

I'm looking at some legacy VB6 code (years+years old, before my time) which runs a query against an SQL 2005 db. It supplies a date restriction in the WHERE clause - where the date is given as an integer value as a result of a CLng() on the Date in VB6.

e.g.

...
WHERE SomeDateField >= 40064

40064 is what VB6 converts today's date to (8th Sep) by doing a CLng() on it. However, in T-SQL this integer actually converts to 10th Sep:

SELECT CAST(40064 AS DATETIME)

And so the results aren't as expected.

Anyone know what may cause this difference in conversion between VB and T-SQL?

I'm assured this always worked without problem, and obviously my suggestion is to pass dates in as dates in standard ISO format. But, need to try to find the reason behind this discrepancy starting to occur.

Answer 1

I think it has something to do with whether there was a 29 february 1900. I believe VB6 thinks there is, but for example JavaScript disagrees. I don't know however how SQL Server handles this; i believe that it also doesn't count feb. 29.

Answer 2

There is a similar issue between Excel and SQL server described in this thread - I imagine it is the same thing?

Answer 3

Seems that VB datetime starts on 30th Dec 1899:

?CDbl(#30/12/1899 03:00:01#)
 0.125011574074074

whereas SQL datetime starts on 1st Jun 1900:

SELECT CAST(0 AS DATETIME)
1900-01-01 00:00:00.000

This gives two days difference which fits your results :).

'VB6
CDbl(#2009-09-08#)
 40064 

-- SQL:
SELECT CAST(40064 AS DATETIME)
2009-09-10 00:00:00.000