将 .NET Ticks 转换为 SQL Server DateTime [英] Convert .NET Ticks to SQL Server DateTime

问题描述

我正在我的数据库中将 TimeSpan(来自 .NET)值保存为 SQL Server 中的 BIGINT(保存 Ticks 属性).我想知道如何将此 BIGINT 值转换为 SQL Server 中的 DATETIME 值(不在 .NET 中).有什么想法吗?

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屋！