将SQL Server DateTime对象转换为BIGINT(.Net ticks)

转换应该在SQL Server 2008中使用T-SQL查询执行

例如：

DateTime value - 12/09/2011 00:00:00

将转换为：

BIGINT value - 634513824000000000

解决方法

我附上上面的文章的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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!