将.NET Ticks转换为SQL Server DateTime

本文介绍了将.NET Ticks转换为SQL Server DateTime的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在SQL Server中保存一个 TimeSpan （from .NET）值作为$ code> BIGINT （保存Ticks财产）。我想知道如何将这个 BIGINT 值转换为SQL Server（而不是.NET）中的 DATETIME 值。任何想法？

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?

干杯

编辑：

我使用NHibernate来映射一个 TimeSpan 属性，它仍然存在Ticks属性。我使用它在一些日期的相对时间（或分钟）控制。

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.

在系统内部一切都很好，这种转换是不需要的。但是，当在SQL Server中执行随机查询时，很难理解 TimeSpan 的持久形式。所以，返回的函数返回的 Ticks 值和 DateTime 将以小时，分钟和秒，即 TimeSpan 表示。

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

实际上，在SQL 2005中可以使用的另一种方法是注意，从0001-01-01到1900-01- 01是599266080000000000.你可以这样做：

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