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

Question

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).

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

Where are you trying to perform the conversion? Can't you fetch the value as a .NET DateTime and then just use the Ticks property? — Jon Skeet, Sep 12 '11 at 10:47
@Jon Skeet Sorry for missing information.. I'm trying to perform the conversion in SQL Server using T-SQL query — Liran Ben Yehuda, Sep 12 '11 at 10:50
+1, while trying to figure out an answer, I learned a few things, so thanks for asking it :-) — Jeff Ogata, Sep 12 '11 at 14:28

score 24 · Answer 1 · answered Sep 12 '11 at 14:27

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

Tx for your detailed post. For one-off conversion its a great solution but it is not my case. — Liran Ben Yehuda, Sep 13 '11 at 05:38

score 20 · Accepted Answer · edited Sep 11 '17 at 14:47

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 believe only DateToTicks fucntion is needed so you canr emove others — sll, Sep 12 '11 at 11:04
I have posted Eron Wright's code verbatim was it wasn't mine, and has stood the test of time (since 2003!). I also note he would be using the DateTimeToTicks function, and that depends on the others. — Aaron, Sep 12 '11 at 11:42

robert4 · Answer 3 · 2015-06-25T05:50:48.017

5

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.

edited Jun 25 '15 at 05:50

answered Jun 24 '15 at 18:48

robert4

1,072
15
20

So I see that `DATETIME2` crashes because `Explicit conversion from data type datetime2 to float is not allowed,` but admit that I don't understand why, precisely. [gbn does, however, provide a solution](https://stackoverflow.com/a/52421814/1028230) by adding one more `CAST`, this time to `DATETIME`: `SELECT ROUND(CAST(CAST(CAST(SYSUTCDATETIME() as DATETIME) AS FLOAT)*8.64e8 AS BIGINT),-1)*1000+599266080000000000` – ruffin Sep 30 '20 at 00:01
Works like a charm also for computing "(TimeSpan = DateTimeB-DateTimeA).Ticks" and converting it to BIGINT as 1 time data migration for me. Thanks. – Honza P. Dec 03 '21 at 15:58

score 3 · Answer 4 · edited Apr 07 '14 at 06:15

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)

score 3 · Answer 5 · answered Sep 04 '14 at 07:45

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

Atleast someone care for refactoring :) +1 – Pranav Singh Jul 16 '15 at 11:00 — Pranav Singh, Jul 16 '15 at 11:00

score 1 · Answer 6 · answered Apr 07 '14 at 06:21

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.

sll · Answer 7 · 2011-09-12T10:57:29.290

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;

score 0 · Answer 8 · answered Apr 25 '17 at 23:36

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

Stoffelche · Answer 9 · 2021-12-17T10:24:07.670

0

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

edited Dec 17 '21 at 10:24

answered Dec 17 '21 at 10:21

Stoffelche

1
1
1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 17 '21 at 10:32

score 0 · Answer 10 · edited Jun 01 '23 at 23:12

0

We can split it up into finding the minutes since 00:00:00 on January 1st 0001 and the microseconds portion of the time. We combine them to get the number of 100 nanoseconds since 00:00:00 on January 1st 0001. This looks like"

600000000*convert(bigint,(datediff(minute, '0001-01-01T00:00:00', datetime)+2880))+datepart(microsecond,datetime)*10

edited Jun 01 '23 at 23:12

Jeremy Caney

7,102
69
48
77

answered Jun 01 '23 at 20:11

Ben Cantor

1

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

10 Answers10

Linked