将 SQL Server DateTime 对象转换为 BIGINT（.Net 刻度）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7386634/
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
Convert SQL Server DateTime object to BIGINT (.Net ticks)
提问by Liran Ben Yehuda
I need to convert a DateTime type value to BIGINT type in .Net ticks format (number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001).
我需要将 DateTime 类型的值转换为 .Net 刻度格式的 BIGINT 类型（自 0001 年 1 月 1 日午夜 12:00:00 起经过的 100 纳秒间隔数）。
The conversion should be perform in Sql server 2008 using T-SQL query
转换应在 Sql server 2008 中使用 T-SQL 查询执行
For example:
例如：
DateTime value - 12/09/2011 00:00:00
will convert to:
将转换为：
BIGINT value - 634513824000000000
回答by Jeff Ogata
I debated whether to post this because it depends on how dates are stored at the binary level in SQL Server, and so it is a very brittle solution. For anything other than a one-off conversion, I would use something like the answer that @Solution Evangelist posted. Still, you might find this interesting in an academic sort of way, so I'll post it anyway.
我争论是否要发布这个，因为它取决于日期如何在 SQL Server 中以二进制级别存储，因此它是一个非常脆弱的解决方案。对于一次性转换以外的任何其他内容，我会使用类似于@Solution Evangelist 发布的答案的内容。不过，您可能会发现这在学术上很有趣，所以无论如何我都会发布它。
Making use of the fact that the accuracy of DateTime2matches up with the tick duration in .NET and that both are based on starting dates of 01-01-0001 00:00:00.0000000, you can cast the DateTimeto DateTime2, and then cast it to binary(9): 0x07F06C999F3CB7340B
利用精度DateTime2与 .NET 中的滴答持续时间相匹配的事实，并且两者都基于 的开始日期01-01-0001 00:00:00.0000000，您可以将 转换DateTime为DateTime2，然后将其转换为binary(9)：0x07F06C999F3CB7340B
The datetime information is stored RTL, so reversing, we'll get 0x0B34B73C9F996CF007.
日期时间信息存储在 RTL 中，因此反过来，我们将得到0x0B34B73C9F996CF007.
The first three bytes store the number of days since 01-01-0001and the next 5 bytes store the 100ns ticks since midnight of that day, so we can take the number of days, multiply by the ticks in a day and add the ticks representing the time elapsed for the day.
前三个字节存储01-01-0001从那天起的天数，接下来的 5 个字节存储从那天午夜开始的 100ns 滴答声，所以我们可以取天数，乘以一天中的滴答声，然后加上代表经过的时间的滴答声那天。
Executing the following code:
执行以下代码：
set @date = getdate()
set @ticksPerDay = 864000000000
declare @date2 datetime2 = @date
declare @dateBinary binary(9) = cast(reverse(cast(@date2 as binary(9))) as binary(9))
declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)
declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)
select @date as [DateTime], @date2 as [DateTime2], @days * @ticksPerDay + @time as [Ticks]
returns the following results:
返回以下结果：
DateTime DateTime2 Ticks
----------------------- ---------------------- --------------------
2011-09-12 07:20:32.587 2011-09-12 07:20:32.58 634514088325870000
Taking the returned number of Ticks and converting back to a DateTime in .NET:
在 .NET 中获取返回的 Ticks 数量并转换回 DateTime：
DateTime dt = new DateTime(634514088325870000);
dt.ToString("yyyy-MM-dd HH:mm:ss.fffffff").Dump();
Gets us back the date from sql server:
从 sql server 获取我们的日期：
2011-09-12 07:20:32.5870000
2011-09-12 07:20:32.5870000
回答by Aaron
I have found a CodeProject article that may assist: Convert DateTime To .NET Ticks Using T-SQL
我找到了一篇可能有帮助的 CodeProject 文章：Convert DateTime To .NET Ticks Using T-SQL
I enclose the SQL function from the above article (I hope this is ok? As it requires registration.)
我附上了上面文章中的 SQL 函数（我希望这没问题？因为它需要注册。）
CREATE FUNCTION [dbo].[MonthToDays365] (@month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN
RETURN
CASE @month
WHEN 0 THEN 0
WHEN 1 THEN 31
WHEN 2 THEN 59
WHEN 3 THEN 90
WHEN 4 THEN 120
WHEN 5 THEN 151
WHEN 6 THEN 181
WHEN 7 THEN 212
WHEN 8 THEN 243
WHEN 9 THEN 273
WHEN 10 THEN 304
WHEN 11 THEN 334
WHEN 12 THEN 365
ELSE 0
END
END
GO
CREATE FUNCTION [dbo].[MonthToDays366] (@month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for leap years
BEGIN
RETURN
CASE @month
WHEN 0 THEN 0
WHEN 1 THEN 31
WHEN 2 THEN 60
WHEN 3 THEN 91
WHEN 4 THEN 121
WHEN 5 THEN 152
WHEN 6 THEN 182
WHEN 7 THEN 213
WHEN 8 THEN 244
WHEN 9 THEN 274
WHEN 10 THEN 305
WHEN 11 THEN 335
WHEN 12 THEN 366
ELSE 0
END
END
GO
CREATE FUNCTION [dbo].[MonthToDays] (@year int, @month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN
RETURN
-- determine whether the given year is a leap year
CASE
WHEN (@year % 4 = 0) and ((@year % 100 != 0) or ((@year % 100 = 0) and (@year % 400 = 0))) THEN dbo.MonthToDays366(@month)
ELSE dbo.MonthToDays365(@month)
END
END
GO
CREATE FUNCTION [dbo].[TimeToTicks] (@hour int, @minute int, @second int)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given hour/minute/second to the corresponding ticks
BEGIN
RETURN (((@hour * 3600) + CONVERT(bigint, @minute) * 60) + CONVERT(bigint, @second)) * 10000000
END
GO
CREATE FUNCTION [dbo].[DateToTicks] (@year int, @month int, @day int)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given year/month/day to the corresponding ticks
BEGIN
RETURN CONVERT(bigint, (((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + dbo.MonthToDays(@year, @month - 1)) + @day) - 1) * 864000000000;
END
GO
CREATE FUNCTION [dbo].[DateTimeToTicks] (@d datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see https://msdn.microsoft.com/en-us/library/system.datetime.ticks(v=vs.110).aspx
BEGIN
RETURN
dbo.DateToTicks(DATEPART(yyyy, @d), DATEPART(mm, @d), DATEPART(dd, @d)) +
dbo.TimeToTicks(DATEPART(hh, @d), DATEPART(mi, @d), DATEPART(ss, @d)) +
(CONVERT(bigint, DATEPART(ms, @d)) * CONVERT(bigint,10000));
END
GO
回答by Mano
you can use below sql to convert a date or utcdate to ticks
您可以使用下面的 sql 将日期或 utcdate 转换为刻度
declare @date datetime2 = GETUTCDATE() or getdate()
declare @dateBinary binary(9) = cast(reverse(cast(@date as binary(9))) as binary(9))
declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)
declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)
select @date as [DateTime], @days * 864000000000 + @time as [Ticks]
and use below sql to convert the tick to a date
并使用下面的 sql 将刻度转换为日期
SELECT Converted = CAST(635324318540000000/864000000000.0 - 693595.0 AS DATETIME)
回答by Aries
Tsk,
啧啧，
Here's the #1 recommendation simplified although I can't believe that was the top result. Surely this is built in, but at this point whatever I'm on a deadline. Refactoring took less than 1 min but maybe it'll help others who are looking for 1 stop solution.
这是简化的＃1建议，尽管我无法相信这是最佳结果。这当然是内置的，但在这一点上，无论我在最后期限。重构只用了不到 1 分钟，但也许它会帮助正在寻找一站式解决方案的其他人。
CREATE FUNCTION [dbo].[Ticks] (@dt DATETIME)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @year INT = DATEPART(yyyy, @dt)
DECLARE @month INT = DATEPART(mm, @dt)
DECLARE @day INT = DATEPART(dd, @dt)
DECLARE @hour INT = DATEPART(hh, @dt)
DECLARE @min INT = DATEPART(mi, @dt)
DECLARE @sec INT = DATEPART(ss, @dt)
DECLARE @days INT =
CASE @month - 1
WHEN 0 THEN 0
WHEN 1 THEN 31
WHEN 2 THEN 59
WHEN 3 THEN 90
WHEN 4 THEN 120
WHEN 5 THEN 151
WHEN 6 THEN 181
WHEN 7 THEN 212
WHEN 8 THEN 243
WHEN 9 THEN 273
WHEN 10 THEN 304
WHEN 11 THEN 334
WHEN 12 THEN 365
END
IF @year % 4 = 0 AND (@year % 100 != 0 OR (@year % 100 = 0 AND @year % 400 = 0)) AND @month > 2 BEGIN
SET @days = @days + 1
END
RETURN CONVERT(bigint,
((((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + @days) + @day) - 1) * 864000000000) +
((((@hour * 3600) + CONVERT(bigint, @min) * 60) + CONVERT(bigint, @sec)) * 10000000) + (CONVERT(bigint, DATEPART(ms, @dt)) * CONVERT(bigint,10000));
END
GO
回答by robert4
I was missing a millisec-accurate one-liner solution to this question, so here is one:
对于这个问题，我缺少一个毫秒级精确的单行解决方案，所以这里是一个：
SELECT ROUND(CAST(CAST(GETUTCDATE() AS FLOAT)*8.64e8 AS BIGINT),-1)*1000+599266080000000000
8.64e8 = TimeSpan.TicksPerDay / 1000
599266080000000000 = DateTime.Parse('1900-01-01').Ticks
8.64e8 = TimeSpan.TicksPerDay / 1000
599266080000000000 = DateTime.Parse('1900-01-01').Ticks
This works for the DATETIME type but not for DATETIME2. The 4/3 ms resolution of DATETIME makes it necessary to involve ROUND(…,-1): after the multiplication by 8.64e8 the float result always ends with either 0 or 33.3 or 66.6. This gets rounded to 0, 30 or 70.
这适用于 DATETIME 类型，但不适用于 DATETIME2。DATETIME 的 4/3 毫秒分辨率使得必须涉及 ROUND(...,-1)：在乘以 8.64e8 之后，浮点结果总是以 0 或 33.3 或 66.6 结束。这将四舍五入为 0、30 或 70。
回答by Nick.McDermaid
A more performant solution might be: (note first two line are just for testing)
一个更高效的解决方案可能是：（注意前两行仅用于测试）
DECLARE @YourDate DATETIME
SET @YourDate = GETDATE()
SELECT
(
(
-- seconds since 1970
CAST(DATEDIFF(s,'1970-01-01 12:00:00',@YourDate) As BIGINT)
)
-- seconds from 0001 to 1970 (approximate)
+ 62125920000
)
-- make those seconds nanoseconds
* 1000000000
Given that your input date only goes down to seconds we just work it out in seconds and multiply by 1000000000 to get nanoseconds.
鉴于您的输入日期只下降到几秒钟，我们只需在几秒钟内计算出来并乘以 1000000000 得到纳秒。
回答by sll
EDIT: Updated due to updated details regarding original question
编辑：由于有关原始问题的更新详细信息而更新
To give such precision of 100ns you should execute algorithm on SQL Server 2008 using new date type - datetime2which accuracy is 100 nanoseconds. Obviously link to an algorithm itself already provided in an other post.
要获得 100ns 的精度，您应该使用新的日期类型 - datetime2在 SQL Server 2008 上执行算法，其精度为 100 纳秒。显然链接到已经在另一篇文章中提供的算法本身。
A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond.
The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001, which represents DateTime.MinValue. It does not include the number of ticks that are attributable to leap seconds.
一个刻度代表一百纳秒或百万分之一秒。一毫秒有 10,000 个滴答声。
此属性的值表示自 0001 年 1 月 1 日午夜 12:00:00 以来经过的 100 纳秒间隔数，它表示 DateTime.MinValue。它不包括可归因于闰秒的滴答数。
DateTime dateTime = DateTime.Now;
System.Int64 ticks = dateTime.Ticks;
回答by John-Perez
This function returns the unix time:
此函数返回 unix 时间：
alter FUNCTION [dbo].[GETTICKS] (@datetime datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
RETURN
DATEPART(millisecond,@datetime) +
CONVERT(bigint,
1000 * (
DATEPART(second,@datetime) +
60 * DATEPART(minute,@datetime) +
3600 * DATEPART(hour,@datetime) +
3600 * 24 * DATEPART(DAYOFYEAR,@datetime) +
convert(bigint, 3600 * 24 * (((DATEPART(year,@datetime) - 1970) * 365) + ((DATEPART(year,@datetime) - 1972) / 4)))
) )
END
