铸造双到DateTime类型是关闭2天？ [英] Casting a double to a DateTime type is off by 2 days?

问题描述

我存储的但是在某些情况下原因无数数据库与SQL float类型（从DateTime.OADate转换），日期时间值是好的，得到人类可读的日期/时间列从数据库返回的。我发现，我可以从表名
执行语句

I store datetime values in the database as sql float type (Converted from an DateTime.OADate) for a myriad of reasons however in certain circumstances it is nice to get a human readable date/time column back from the database. I have found that I can execute the statement

SELECT CAST (timerecorded_utc as DATETIME) FROM tablename

和它会给我我找的日期时间字符串，但它似乎正好2天关闭。我知道我可以只修改语句（因为在表示为double 1天时间就= 1.0）为

and it will give me the date time string I am looking for but it seems to be off by exactly 2 days. I realize I can just modify the statement (since in time represented as a double 1 day = 1.0) to be

SELECT CAST (timerecorded_utc-2.0 as DATETIME) FROM tablename

但我想知道这是否是一致的，在我看来，有一些原因，我很想念的差异。

BUT I was wondering if this is consistent AND it seems to me there is some reason for the discrepancy that I am missing.

推荐答案

这是因为日期使用时期是不同的。

It's because the epochs the dates use are different.

SQL Server的DATETIME使用01/01/1900 00:00:00的时代，你可以通过运行下面的查询看到： SELECT CAST（0 AS DATETIME）

SQL Server's DATETIME uses 01/01/1900 00:00:00 as the epoch, which you can see by running the following query: SELECT CAST(0 AS DATETIME)

OADate是一个有点古怪，因为它可能有30/12/1899 0时00分00秒或31开天辟地/一千八百九十九分之十二00:00:00取决于你是否相信分别在Visual Basic或Excel的家伙。这样看来，从你的2天差异，.NET版本去与30日。

OADate is a bit odd, as it could have an epoch of 30/12/1899 00:00:00 or 31/12/1899 00:00:00 depending on whether you believe the Visual Basic or Excel guys, respectively. It would appear that from your two day difference, the .NET version goes with the 30th.

所以，两天划时代关给出的结果2天差异，当你这两种类型的日期之间通过原始的数字转换。

So, epoch off by two days gives two days difference in the outcome when you convert between the two types of date via a raw number.

这篇关于铸造双到DateTime类型是关闭2天？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！