将.NET Ticks转换为SQL Server DateTime [英] Convert .NET Ticks to 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')

这篇关于将.NET Ticks转换为SQL Server DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！