将SQL Server DateTime对象转换为BIGINT(.Net滴答) [英] Convert SQL Server DateTime object to BIGINT (.Net ticks)
本文介绍了将SQL Server DateTime对象转换为BIGINT(.Net滴答)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要将.Net tick格式(自0001年1月1日午夜12:00:00开始经过的100纳秒间隔数)转换为DateTime类型值到BIGINT类型。
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).
转换应在Sql Server 2008中使用T-SQL查询
The conversion should be perform in Sql server 2008 using T-SQL query
例如:
DateTime value - 12/09/2011 00:00:00
将转换为:
BIGINT value - 634513824000000000
推荐答案
我发现了一个CodeProject文章可以提供帮助:使用T-SQL将DateTime转换为.NET标记
I have found a CodeProject article that may assist: Convert DateTime To .NET Ticks Using T-SQL
我附上了SQL函数从上面的文章(我希望这可以吗?因为它需要注册。)
I enclose the SQL function from the above article (I hope this is ok? As it requires registration.)
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
这篇关于将SQL Server DateTime对象转换为BIGINT(.Net滴答)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文