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