問題描述

我正在查看一些對 SQL 2005 db 運(yùn)行查詢的舊版 VB6 代碼(在我的時(shí)代之前).它在 WHERE 子句中提供了日期限制 - 其中日期作為整數(shù)值給出，作為 VB6 中日期上的 CLng() 的結(jié)果.

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 通過對其執(zhí)行 CLng() 將今天的日期轉(zhuǎn)換為(9 月 8 日)的內(nèi)容.然而，在 T-SQL 這個(gè)整數(shù)實(shí)際上轉(zhuǎn)換為 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)

所以結(jié)果并不如預(yù)期.

有誰知道是什么導(dǎo)致了 VB 和 T-SQL 之間的這種轉(zhuǎn)換差異?

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

我確信這總是沒有問題，顯然我的建議是以標(biāo)準(zhǔn) ISO 格式將日期作為日期傳遞.但是，需要嘗試找出這種差異開始出現(xiàn)的原因.

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 日期時(shí)間從 1899 年 12 月 30 日開始:

Seems that VB datetime starts on 30th Dec 1899:

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

而 SQL 日期時(shí)間從 1900 年 6 月 1 日開始:

whereas SQL datetime starts on 1st Jun 1900:

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

這給出了兩天的差異，適合您的結(jié)果:)

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

這篇關(guān)于INTEGER 到 DATETIME 的轉(zhuǎn)換與 VB6 不同的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！