将日期时间转换为浮点数时，SQL 的转换函数如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4141399/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How SQL's convert function work when converting datetime to float?
提问by Jeevan Bhatt
why the out put of this query:
为什么这个查询的输出：
declare @currentDate as datetime
set @currentDate ='01/07/2010'
select convert(float, @currentdate)
...is 40183 ?
...是 40183 吗？
So for those who are getting confuse with my question, my question is How to know the result of above query without executing it ?
所以对于那些对我的问题感到困惑的人，我的问题是如何在不执行的情况下知道上述查询的结果？
采纳答案by jachguate
DateTime is often represented as a day count from a pre-determined date (generally know as the epoch) on the integer part and the percentage of the day elapsed since mid-night on the fractional part.
DateTime 通常表示为整数部分的预定日期（通常称为纪元）的天数和小数部分自午夜以来经过的一天的百分比。
SQL Server is not the exception to this, thus the conversion to Float makes a lot of sense. Day 0 is Jan 01 1900 00:00:00 (AFAIK, in no particular time-zone, so you shall consider it "local time").
SQL Server 也不例外，因此转换为 Float 很有意义。第 0 天是 1900 年 1 月 1 日 00:00:00（AFAIK，没有特定时区，因此您应将其视为“当地时间”）。
So, you can try this:
所以，你可以试试这个：
declare @ADate DateTime;
set @ADate = '19000101 00:00:00';
select CONVERT(float, @ADate); --should print 0
set @ADate = '19000101 12:00:00';
select CONVERT(float, @ADate); --should print 0.5
set @ADate = '19001231 06:00:00';
select CONVERT(float, @ADate); --should print 364.25
So, for your results, 40183 days has been passed since 01/01/1900 00:00:00 and 01/07/2010 00:00:00
因此，对于您的结果，自 01/01/1900 00:00:00 和 01/07/2010 00:00:00 以来已经过去了 40183 天
Clarification: Unix like systems use a different approach to store datetimes: Seconds since Unix epoch (Jan 1 1970 00:00:00 UTC), which is more known as epoch time.
说明：类 Unix 系统使用不同的方法来存储日期时间：自 Unix 纪元（1970 年 1 月 1 日 00:00:00 UTC）以来的秒数，这更多地称为纪元时间。
[Edit] Date format on this response was changed to YYYYMMDD format on 20140416, after some new years of experience with SQL Server (and as @Damien said in his comment) this is the only safe format.
[编辑] 此响应中的日期格式在 20140416 上更改为 YYYYMMDD 格式，经过几年的 SQL Server 经验（正如@Damien 在评论中所说），这是唯一安全的格式。
回答by Thomas
DateTime values are actually stored as two four-byte integers under the hood. The first four-byte integer represents the number of days since 1900-01-01. The second four-byte integer stores the number of milliseconds since midnight. When you convert a datetime into a float, the decimal portion represents the percentage of the 24-day that has past. Thus, 0.5 represents noon.
DateTime 值实际上存储为两个四字节整数。第一个四字节整数表示自 1900-01-01 以来的天数。第二个四字节整数存储自午夜以来的毫秒数。将日期时间转换为浮点数时，小数部分表示过去 24 天的百分比。因此，0.5 代表中午。
回答by Brannon
It's basically converting the datetimeto an OLE Date. There's a decent description of the process in the documentation for System.DateTime.ToOADate():
它基本上是将 转换datetime为 OLE 日期。文档中有一个对过程的体面描述System.DateTime.ToOADate()：
http://msdn.microsoft.com/en-us/library/system.datetime.tooadate.aspx
http://msdn.microsoft.com/en-us/library/system.datetime.tooadate.aspx
The quick explanation is that the integer part is the number of days since 12/30/1899. The fractional part (zero in this case) is the time divided by 24.
快速解释是整数部分是自 1899 年 12 月 30 日以来的天数。小数部分（在这种情况下为零）是时间除以 24。
回答by Vedran
This should help you understand the TSQL implementation (or implement your own):
这应该可以帮助您了解 TSQL 实现（或实现您自己的）：
DECLARE
@date DATETIME = '20180125 09:15:30.549',
@date_dec DECIMAL (26,10) = 43123.3857702546
SELECT
CAST(@date_dec AS DATETIME) AS [TSQL cast to DATETIME],
CAST(@date AS DECIMAL (26,10)) AS [TSQL cast to DECIMAL]
SELECT
DATEADD(DAY, FLOOR(@date_dec),
DATEADD(HOUR, FLOOR(@date_dec % 1 * 24),
DATEADD(MINUTE, FLOOR((@date_dec % 1 * 24) % 1 * 60),
DATEADD(SECOND, FLOOR(((@date_dec % 1 * 24) % 1 * 60) % 1 * 60),
DATEADD(MILLISECOND, FLOOR((((@date_dec % 1 * 24) % 1 * 60) % 1 * 60) % 1 * 1000), '19000101')
)
)
)
) AS [Manual cast to DATETIME],
DATEDIFF(DAY, '19000101', @date)
+ (
DATEPART(HOUR, @date)
+ (
DATEPART(MINUTE, @date)
+ (
DATEPART(SECOND, @date)
+ DATEPART(MILLISECOND, @date) / CAST(1000 AS FLOAT)
) / CAST(60 AS FLOAT)
) / CAST(60 AS FLOAT)
) / CAST(24 AS DECIMAL (26,10)) AS [Manual cast to DECIMAL]
Note that the result is not always the same as TSQL loses precision on last millisecond digit.
请注意，结果并不总是与 TSQL 在最后一毫秒数字上失去精度相同。
