SwastikMS (3/12/2014)
I used the below statement:e.g.,
SELECT dateadd(hh,5.30,CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME))
It's giving me a difference of approx. 30 minutes.
When I don't add the UTC difference, the result has a huge difference.
I am using Sql Server 2012.
Thanks...
I tested it on a SQL Server 2008 instance as I don't have a SQL Server 2012 instance.
SELECT dateadd(hh,5.30,CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME)),
CAST(635302120392663258/864000000000.0 - 693595.0 AS DATETIME)
The above produces 2014-03-12 14:07:19.263,2014-03-12 09:07:19.263
Note that if you are trying to add 5 hours and 30 minutes to get your local time, 5.30 will not work. The second parameter to dateadd is an int. Here is the relevant documentation for dateadd. http://msdn.microsoft.com/en-us/library/ms186819(SQL.105).aspx. Convert 5 hours and 30 minutes entirely to minutes and add that number as minutes.
new DateTime(635302120392663258, DateTimeKind.Utc).ToString("yyyy-MM-dd HH:mm:ss:fff")
The above in LinqPad produces 2014-03-12 09:07:19:266 which is the exact UTC time for 635302120392663258.
Jedak