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