Convert .NET DateTime.Ticks to T-SQL datetime

It's not very uncommon to store DateTime values as UTC Ticks count in a bigint SQL Server column (or something equal on other DB engines). This gives total control over timezone shifts, DST, UTC/Local time problem, etc. Ticks are ticks.
While it's rather easy to work with this in .NET code:

var valueToStoreInDb = dateTimeVariable.ToUniversal().Ticks;

var dateTimeValue = new DateTime(ticksFromDb, DateTimeKind.Utc);

Accessing the data from SQL (e.g. in a simple SELECT query written by a DBA) is painful, because huge numbers are absolutely not human-readable.

The obvious way to solve the problem is creating a User-Defined Function on DB server. I tried to search the internet for such functions and have found only limited-precision solutions, like this one which is accurate to the minute. I don't know why the author did not implemented a full-precision solution. Perhaps, it was due to the fact that minute is the lowest unit the number passed from the 1900 to today can be stored in an int variable. However, it's not difficult to write a function with top possible precision for datetime type.



The idea for the solution comes from the MSDN documentation for the SQL Server 2005 datetime type. Since they store datetime as two integers, one for the day and the other for the time of day, we can split the .Net ticks count into corresponding parts. Here is the code:

CREATE FUNCTION NetFxUtcTicksToDateTime
(
 @Ticks bigint
)
RETURNS datetime
AS
BEGIN

-- First, we will convert the ticks into a datetime value with UTC time
 DECLARE @BaseDate datetime;
 SET @BaseDate = '01/01/1900';
 
 DECLARE @NetFxTicksFromBaseDate bigint;
 SET @NetFxTicksFromBaseDate = @Ticks - 599266080000000000;
-- The numeric constant is the number of .Net Ticks between the System.DateTime.MinValue (01/01/0001) and the SQL Server datetime base date (01/01/1900)
 
 DECLARE @DaysFromBaseDate int;
 SET @DaysFromBaseDate = @NetFxTicksFromBaseDate / 864000000000;
-- The numeric constant is the number of .Net Ticks in a single day.
 
 DECLARE @TimeOfDayInTicks bigint;
 SET @TimeOfDayInTicks = @NetFxTicksFromBaseDate - @DaysFromBaseDate * 864000000000;
 
 DECLARE @TimeOfDayInMilliseconds int;
 SET @TimeOfDayInMilliseconds = @TimeOfDayInTicks / 10000;
-- A Tick equals to 100 nanoseconds which is 0.0001 milliseconds
 
 DECLARE @UtcDate datetime;
 SET @UtcDate = DATEADD(ms, @TimeOfDayInMilliseconds, DATEADD(d, @DaysFromBaseDate, @BaseDate));
-- The @UtcDate is already useful. If you need the time in UTC, just return this value.
 
-- Now, some magic to get the local time
 RETURN @UtcDate + GETDATE() - GETUTCDATE();
END
GO

This function converts a 64-bit integer to a datetime value with millisecond precision in server local time.