Blog‎ > ‎IT‎ > ‎

SQL map time to .Net ticks

posted Nov 12, 2015, 5:38 PM by Jake Vosloo
.Net System.DateTime.Ticks has 10,000,000 ticks per second and starts from 0:00:00 UTC on 1 January 0001, in the Gregorian calendar. To get this number in SQL is quite complex but here is a shortcut:

This select statement calculates the .Net ticks in SQL and is accurate to the nearest second.
select (cast(630822816000000000  as bigint) + cast(datediff(second,'2000-01-01',GETUTCDATE()) as bigint) * 10000000) as [Ticks.Net]

This is how I constructed the formula, first I got the ticks up to 2000 from powershell as follows:
Write-Host "2000:  " ([System.DateTime]"2000-01-01").Ticks
Write-Host "Now:   " ([DateTime]::UtcNow).Ticks
Write-Host "Seconds:          ^       " 

Which returns:
2000:   630822816000000000

Then I calculate the difference in seconds since 2000 and the current date and multiply the number of seconds with 10 million to get it into ticks.