.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,'20000101',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: WriteHost "2000: " ([System.DateTime]"20000101").Ticks
WriteHost "Now: " ([DateTime]::UtcNow).Ticks
WriteHost "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.
References:
