nhibernate - Convert .NET Ticks to SQL Server DateTime

Question

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.

Answer 1

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.