Convert .NET Ticks to SQL Server DateTime

user_267153 0
Viewed 40250

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.

timespan sql-server-2005 datetime nhibernate
7 Answers

I'm not sure how accurate this will be with the seconds, but you could try something like:

Declare @TickValue bigint
Declare @Days float

Set @TickValue = 634024345696365272 
Select @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24

Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE)) 
    + Cast( (@Days - FLOOR(@Days)) As DateTime)

Actually another way that would work in SQL 2005 is to note that the the number of ticks from 0001-01-01 to 1900-01-01 is 599266080000000000. With that you could do:

Declare @TickOf19000101 bigint
Declare @TickValue bigint
Declare @Minutes float

Set @TickOf19000101  = 599266080000000000
Set @TickValue = DATEDIFF(mi, 0 ,CURRENT_TIMESTAMP) * Cast(60 As BigInt) 
                   * POWER(10.00000000000,7) + @TickOf19000101

Select @TickValue
Select @Minutes = (@TickValue - @TickOf19000101) * POWER(10.00000000000,-7) / 60

Select @Minutes
Select DATEADD(MI, @Minutes, '1900-01-01')
user_1986430

A TimeSpan is not a date, and saving it as such may cause confusion in the future.

Is there a reason you can't simply save the ticks to an integer field and not change its meaning?

Oded_15830

Get the value of TimeSpan.TicksPerSecond in .NET (just write it down).

Then, in your SQL, you can divide the tick count by that number, to give the number of seconds.

You can then divide this by 60 to get minutes, etc.

user_956360

You should be able to use the CAST function built into SQL Server. 

SELECT(CAST(CAST(CAST ('02/02/10' AS datetime) AS BIGINT) AS datetime))

you get 2010-02-02 00:00:00.000

user_918140

I have figured it out on my own:

288,000,000,000 ticks represents 8 hours, so the following SELECT returns a dummy date with the ammount of hours specified ...

SELECT DATEADD(millisecond, 288000000000/10000, CAST('1900-01-01' AS DATETIME))

Thanks to everyones efforts.

user_2671530
Related