Michael J. Swart

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.

ticks

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

2 Comments »

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

    Comment by Michael J. Swart — July 6, 2017 @ 11:27 am

  2. […] Michael J. Swart shows you how to convert DATETIME2 values to Ticks: […]

    Pingback by Avoid Ticks – Curated SQL — July 7, 2017 @ 7:59 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress