I am saving a TimeSpan
(from .NET) value in my db as BIGINT
in SQL Server (saving the Ticks property). I want to know how to convert this BIGINT
value to a DATETIME
value in SQL Server (not in .NET). Any ideas?
Cheers
EDIT:
I am using NHibernate to map a TimeSpan
property I have, and it persists the Ticks property. I use it for relative hours (or minutes) control over some date.
Internally in the system everything is fine, this conversion isn't needed. However, when performing random queries in SQL Server, it is hard to understand the persisted form of a TimeSpan
. So, a function where I pass the Ticks
value and a DateTime
is returned would give the amount in hours, minutes and seconds that that TimeSpan
represents.
I don't really know SQL Server, but today a colleague of mine had the same problem and I think I've found a solution like this:
CAST(([ticks] - 599266080000000000) / 10000000 / 24 / 60 / 60 AS datetime)
where 599266080000000000 is the ticks value for 01/01/1900 00:00:00.