我需要将DateTime类型值转换为.Net ticks格式的BIGINT类型(0001年1月1日凌晨12:00之后已经过去的100纳秒间隔).
转换应该在sql Server 2008中使用T-SQL查询执行
例如：
DateTime value - 12/09/2011 00:00:00
将转换为：
BIGINT value - 634513824000000000
解决方法
我找到了可以协助的CodeProject文章：
Convert DateTime To .NET Ticks Using T-SQL
我附上上面的文章的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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstickstopic.asp
BEGIN
RETURN
dbo.DateToTicks(DATEPART(yyyy,@d),DATEPART(mm,DATEPART(dd,@d)) +
dbo.TimeToTicks(DATEPART(hh,DATEPART(mi,DATEPART(ss,@d)) +
(CONVERT(bigint,DATEPART(ms,@d)) * CONVERT(bigint,10000));
END
GO
