INTEGER 到 DATETIME 的转换与 VB6 不同

2021-09-10 00:00:00 sql tsql sql-server vb6

我正在查看一些对 SQL 2005 db 运行查询的旧版 VB6 代码(在我的时代之前).它在 WHERE 子句中提供了日期限制 - 其中日期作为整数值给出，作为 VB6 中日期上的 CLng() 的结果.

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.

例如

... WHERE SomeDateField >= 40064

40064 是 VB6 通过对其执行 CLng() 将今天的日期转换为(9 月 8 日)的内容.然而，在 T-SQL 这个整数实际上转换为 10th Sep:

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)

所以结果并不如预期.

有谁知道是什么导致了 VB 和 T-SQL 之间的这种转换差异?

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

我确信这总是没有问题，显然我的建议是以标准 ISO 格式将日期作为日期传递.但是，需要尝试找出这种差异开始出现的原因.

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.

推荐答案

似乎 VB 日期时间从 1899 年 12 月 30 日开始:

Seems that VB datetime starts on 30th Dec 1899:

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

而 SQL 日期时间从 1900 年 6 月 1 日开始:

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

相关文章