Access和SQL Server计算日期的方式不同 [英] Access and SQL Server calculate date differently
问题描述
我们有一些通用工具,除其他外,可以保存和显示日期. 它将另存为数字,例如41247.
We have some generic tool that, among others, can save and display dates. It will save it as a number, for example 41247.
如果我将其转换回Access中的日期,则会得到2012/12/04-是正确的.
[使用Format(41247;"General Date")或Format(Format("41247","Short Date"),"Short Date")]
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")]
如果我将该数字转换回SQL Server中的日期,则会得到2012/12/06-不正确. 我正在使用CONVERT(datetime,CONVERT(real,41247))
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))
为什么有区别,我可以在SQL中使用什么来解决它?
Why the difference, and what can I use in SQL to fix it?
推荐答案
因此,当我从excel文件导入数据时遇到了这个问题.
So I had this issue when I was importing data from an excel file.
您有2天的时间差异有两个原因
There are two reasons you have 2 days difference
原因1 在您的SQL Server中,1900年1月1日是第0天,而在访问中是第1天.
Reason 1 In your SQL Server Jan 1, 1900 is Day 0, while in access it is Day 1.
(我没有使用Access,但是如果您在Excel中输入日期1900-01-01并将单元格格式设置为数字,则会得到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).
原因2 1900年不是a年. SQL Server知道它,但Access不知道.它认为存在1900年2月29日.
Reason 2 1900 was NOT a leap year. SQL Server knows it but Access doesn't. It thinks Feb 29, 1900 existed.
在您的SQL Server中运行
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')
输出将是
0
58
59
但是当您尝试运行
SELECT DATEDIFF(dd, 0, '1900-02-29')
您会得到一个错误
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
由于这两个原因,您的访问时间又增加了2天.
Due to these 2 reasons you get 2 days more in your Access.
因此,对于2012/12/04,Access返回41247,而SQL Server将给您41245.
So for 2012/12/04 Access returns 41247, while SQL Server will give you 41245.
希望有帮助.
编辑
在埃里克·利珀特(Eric Lippert)的评论中,他提到了他和乔尔·斯波斯基(Joel Spolsky)撰写的2篇非常有趣的博客文章.
Have a look at Eric Lippert's comments where he has mentioned 2 really interesting blog posts by him and Joel Spolsky.
这篇关于Access和SQL Server计算日期的方式不同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!