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.

Nihhi

Answer 1

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')

Grundy

Granted..this requires SQL 2008 and its DATE data type. – Grundy

Btw, it should also be noted that the Ticks value starts from '0001-01-01' not '1900-01-01'. – Grundy

The code is great, but if the timespan represents hours only and not a full date, the resulting date is too low to be represented as a datetime. But the code is a good start, I'll mark it as answer and work on it. Thank you very much! – Nihhi

This worked great for looking into my quartz.net triggers: DATEADD(hour,@EST_offset_h,DATEADD(mi,(a.START_TIME - 599266080000000000) * POWER(10.00000000000,-7) / 60, '1900-01-01')) AS 'StartDateTimeEST-4' – Edora

Answer 2

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 / 60 / 60 / 24 AS datetime)

where 599266080000000000 is the ticks value for 01/01/1900 00:00:00 UTC and 10000000 is the number of ticks in a second.

Anatto

This is really a reiteration of the previous answers, but I think it has some merit because it looks simpler to me. – Anatto

Answer 3

You can use this function taken from Pavel Gatilov's blog to convert a 64-bit integer to a datetime value with millisecond precision in server local time:

CREATE FUNCTION NetFxUtcTicksToDateTime
(
   @Ticks bigint
)
RETURNS datetime
AS
BEGIN

-- First, we will convert the ticks into a datetime value with UTC time
DECLARE @BaseDate datetime;
SET @BaseDate = '01/01/1900';

DECLARE @NetFxTicksFromBaseDate bigint;
SET @NetFxTicksFromBaseDate = @Ticks - 599266080000000000;
-- The numeric constant is the number of .Net Ticks between the System.DateTime.MinValue (01/01/0001) and the SQL Server datetime base date (01/01/1900)

DECLARE @DaysFromBaseDate int;
SET @DaysFromBaseDate = @NetFxTicksFromBaseDate / 864000000000;
-- The numeric constant is the number of .Net Ticks in a single day.

DECLARE @TimeOfDayInTicks bigint;
SET @TimeOfDayInTicks = @NetFxTicksFromBaseDate - @DaysFromBaseDate * 864000000000;

DECLARE @TimeOfDayInMilliseconds int;
SET @TimeOfDayInMilliseconds = @TimeOfDayInTicks / 10000;
-- A Tick equals to 100 nanoseconds which is 0.0001 milliseconds

DECLARE @UtcDate datetime;
SET @UtcDate = DATEADD(ms, @TimeOfDayInMilliseconds, DATEADD(d, @DaysFromBaseDate, @BaseDate));
-- The @UtcDate is already useful. If you need the time in UTC, just return this value.

-- Now, some magic to get the local time
RETURN @UtcDate + GETDATE() - GETUTCDATE();
END
GO

Alternative code suitable for inline usage:

DECLARE @Ticks bigint
set @Ticks = 634899090000000000
select DATEADD(ms, ((@Ticks - 599266080000000000) - 
   FLOOR((@Ticks - 599266080000000000) / 864000000000) * 864000000000) / 10000,
   DATEADD(d, (@Ticks - 599266080000000000) / 864000000000, '01/01/1900')) +
   GETDATE() - GETUTCDATE()

Thicken

Beware that this function can return datetimes that differ by a few ms for the same input ticks value. The return statement makes two distinct calls to get the current datetime, and time can (sometimes) have moved on between those calls. – Deutsch

+1 for the rather brilliant UTC -> Local conversion. There's article after article online telling people to create timezone offset tables and such, and then there's this, so simple and buried in a seemingly unrelated topic. – Electrify

The UTC -> local conversion is useful only if the date you are comparing is recent. If you are using a date that you are not sure if it was during Daylight Savings (A.K.A. summer time) then you still need to do some comparisons to convert properly. Here is a good example to get started with that – Edora

Answer 4

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?

Strangulation

Answer 5

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.

Jinnah

Answer 6

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

Trichology

Strangely going from ticks and using select (CAST(CAST(633979266000000000 AS BIGINT) as datetime)) gives an Arithmetic overflow error. – Hhd

Answer 7

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.

Nihhi