Converting from DateTime to Ticks using SQL Server

July 6, 2017

Converting from DateTime to Ticks using SQL Server

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 9:55 am

A .Net tick is a duration of time lasting 0.1 microseconds. When you look at the Tick property of DateTime, you’ll see that it represents the number of ticks since January 1st 0001.
But why 0.1 microseconds? According to stackoverflow user CodesInChaos “ticks are simply the smallest power-of-ten that doesn’t cause an Int64 to overflow when representing the year 9999”.

Even though it’s an interesting idea, just use one of the datetime data types, that’s what they’re there for. I avoid ticks whenever I can.

But sometimes things are out of your control. If you do want to convert between datetime2 and ticks inside SQL Server instead of letting the application handle it, then you need to do some date math. It can get a bit tricky avoiding arithmetic overflows and keeping things accurate:

DateTime2 to Ticks

CREATE FUNCTION dbo.ToTicks ( @DateTime datetime2 )
  RETURNS bigint
AS
BEGIN
 
    RETURN DATEDIFF_BIG( microsecond, '00010101', @DateTime ) * 10 +
           ( DATEPART( NANOSECOND, @DateTime ) % 1000 ) / 100;
END

DateTime2 to Ticks (Versions < SQL Server 2016)

CREATE FUNCTION dbo.ToTicks ( @DateTime datetime2 )
  RETURNS bigint
AS
BEGIN
    DECLARE @Days bigint = DATEDIFF( DAY, '00010101', cast( @DateTime as date ) );
    DECLARE @Seconds bigint = DATEDIFF( SECOND, '00:00', cast( @DateTime as time( 7 ) ) );
    DECLARE @Nanoseconds bigint = DATEPART( NANOSECOND, @DateTime );
    RETURN  @Days * 864000000000 + @Seconds * 10000000 + @Nanoseconds / 100;
END

Ticks to DateTime2

CREATE FUNCTION dbo.ToDateTime2 ( @Ticks bigint )
  RETURNS datetime2
AS
BEGIN
    DECLARE @DateTime datetime2 = '00010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END

-- Comments (6)

6 Comments »

This has nothing to do with @@TIMETICKS.
My advice stands, avoid ticks.

Comment by Michael J. Swart — July 6, 2017 @ 11:27 am
[…] Michael J. Swart shows you how to convert DATETIME2 values to Ticks: […]

Pingback by Avoid Ticks – Curated SQL — July 7, 2017 @ 7:59 am
I’m a bit in a dubio because of this one, while datetime is handy it’s also SLOW!

It’s okay for little databases, but you are working with millions of entries, try order with datetime it’s awfully slow. Works pretty fast when sorting by int though.

Comment by laurens — August 11, 2018 @ 1:43 pm
Also I think the conversion could happen programmatically, let the app handle this not database. Database is expensive.

Also when you are getting 100 records from the database does not mean you need to use the datetime for all 100 records. Perhaps you need to convert only 25? while if you let SQL handle this it will always do 100.

Comment by laurens — August 11, 2018 @ 1:48 pm
I’m using a Quartz scheduler that works in ticks. This is very useful, thank you!

Comment by Henry Troup — October 18, 2019 @ 2:04 pm
This is awesome – dno’t know how many times I’ve used this code when I forget how to do this conversion and I don’t have the function defined in new customer databases I set up.
(If I knew how to do a pingback I would do one!)

Comment by Mike Christie — October 20, 2020 @ 2:29 am