Convert SQL Server DateTime object to BIGINT (.Net ticks)

I need to convert a DateT开发者_如何学Pythonime 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).

The conversion should be perform in Sql server 2008 using T-SQL query

For example:

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

will convert to:

BIGINT value - 634513824000000000

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.

Making use of the fact that the accuracy of DateTime2 matches 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 DateTime to DateTime2, and then cast it to binary(9): 0x07F06C999F3CB7340B

The datetime information is stored RTL, so reversing, we'll get 0x0B34B73C9F996CF007.

The first three bytes store the number of days since 01-01-0001 and 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.

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:

DateTime dt = new DateTime(634514088325870000);
dt.ToString("yyyy-MM-dd HH:mm:ss.fffffff").Dump();

Gets us back the date from sql server:

2011-09-12 07:20:32.5870000

I have found a CodeProject article that may assist: 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.)

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

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

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.

you can use below sql to convert a date or utcdate to ticks

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

SELECT Converted = CAST(635324318540000000/864000000000.0 - 693595.0 AS DATETIME)

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.

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

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 - datetime2 which accuracy is 100 nanoseconds. Obviously link to an algorithm itself already provided in an other post.

DateTime.Ticks Property

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.

DateTime dateTime = DateTime.Now;
System.Int64 ticks = dateTime.Ticks;

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.

This function returns the unix time:

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

CREATE FUNCTION UtcToTicks( @d datetime) RETURNS bigint
   AS
   BEGIN
       declare @d1 datetime
       set @d1 = convert(datetime,0)
       declare @days bigint = DATEDIFF(d,@d1,@d)
       declare @time bigint = DATEDIFF(ms,convert(datetime,@days),@d)
       return 599266080000000000 + 864000000000 * @days + @time * 10000
   END