将 double 转换为 DateTime 类型会缩短 2 天吗？
由于多种原因，我将日期时间值存储在数据库中作为 sql float 类型（从 DateTime.OADate 转换），但是在某些情况下，从数据库返回人类可读的日期/时间列是很好的。我发现我可以执行该语句
SELECT CAST (timerecorded_utc as DATETIME) FROM tablename
，它会给我我正在寻找的日期时间字符串，但它似乎正好相差 2 天。我意识到我可以将语句修改为（因为时间表示为双 1 天 = 1.0），
SELECT CAST (timerecorded_utc-2.0 as DATETIME) FROM tablename
但我想知道这是否一致，并且在我看来，我缺少的差异似乎是有某种原因的。
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
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是因为日期使用的纪元不同。
SQL Server 的 DATETIME 使用 01/01/1900 00:00:00 作为纪元，您可以通过运行以下查询来查看：
SELECT CAST(0 AS DATETIME)OADate 有点奇怪，因为它纪元可能是 30/12/1899 00:00:00 或 31/12/1899 00:00:00，具体取决于您是否相信 Visual Basic 或 Excel 人员。从两天的差异来看，.NET 版本似乎是在 30 号。
因此，当您通过原始数字在两种类型的日期之间进行转换时，两天的纪元会产生两天的结果差异。
It's because the epochs the dates use are different.
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 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.
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.
Epic Epochs...这是我在 SQL Server 中使用内置的 TSQL 解决方案
“DateAdd”函数：
在我的例子中，我通过 C# Core App 导入保存在 Excel 中的字符串并上传到 SQL Server 数据库，因此我的 [结束日期] 是一个字符串，我将其转换为不带精度的小数，因为我只需要实际日期，而不是一天中的时间。正如 @GregBeech 提到的，您的基准日期可能是“1899 年 12 月 31 日”。
Epic Epochs... Here is my TSQL solution in SQL Server using the built in
"DateAdd" function:
In my case I was importing a string saved in Excel via C# Core App and uploading to a SQL Server database so my [Ending Date] is a string which I casted as a decimal with no precision as I only needed the actual date, and not the time of day. As @GregBeech mentioned, your base date might be '12/31/1899'.