使用SqlDataAdapter时，将SQL Server DATEDIFF列作为TimeSpan获取

Is it possible to get the result of the DATEDIFF function in SQL Server as a TimeSpan when filling a DataTable using SqlDataAdapter?

在使用SqlDataAdapter填充DataTable时，是否可以将SQL Server中DATEDIFF函数的结果作为TimeSpan获取？

As a very minimal example:

作为一个非常小的例子：

var table = new DataTable();
SqlCommand cmd = new SqlCommand(@"select DATEDIFF(mi, '2016-01-01', '2016-02-02') as [foo];", conn);
var da = new SqlDataAdapter(cmd);
da.Fill(table);
Console.WriteLine(table.Columns[0].DataType);

This prints out System.Int32 instead of TimeSpan, and I can't change the DataType after the table has been already filled with table.Columns[0].DataType = typeof(TimeSpan);, because it would throw an exception.

这打印出System.Int32而不是TimeSpan，并且在表已经填充table.Columns [0] .DataType = typeof（TimeSpan）;之后我无法更改DataType，因为它会引发异常。

I could create an entirely new DataTable and copy the data into it, but I'd rather not do that.

我可以创建一个全新的DataTable并将数据复制到其中，但我宁愿不这样做。

3 个解决方案

#1

just define it before select:

在选择之前定义它：

    var table = new DataTable();
    SqlCommand cmd = new SqlCommand(@"select DATEDIFF(mi, '2016-01-01', '2016-02-02') as [foo];", conn);
    var da = new SqlDataAdapter(cmd);
    table.Columns.Add("foo", typeof(TimeSpan));
    da.Fill(table);
    Console.WriteLine(table.Columns[0].DataType);

EDIT

编辑

but be careful. you are using DATEDIFF with mi parameter. but when you maps foo to TimeSpan it means that timespan will be created with this amount of minutes interpreted as Ticks.

不过要小心。你正在使用带有mi参数的DATEDIFF。但是当你将foo映射到TimeSpan时，这意味着将创建时间跨度，并将此分钟数量解释为Ticks。

So to correct it you need to do something like this

所以要纠正它你需要做这样的事情

select DATEDIFF(mcs, '2016-01-01', '2016-02-02')*10 as [foo]

Since ticks is 100 nanosecond units.

因为滴答是100纳秒单位。

but in most cases it will cause to SqlException: The datediff function resulted in an overflow.

但在大多数情况下，它会导致SqlException：datediff函数导致溢出。

#2

SQL Server doesn't have a data type that maps automatically to a .Net TimeSpan. You usually have to store the span as an Int (or BigInt) and convert it to a TimeSpan as you read from the adapter.

SQL Server没有自动映射到.Net TimeSpan的数据类型。您通常必须将跨度存储为Int（或BigInt），并在从适配器读取时将其转换为TimeSpan。

Check out this post for some examples.

查看这篇文章了解一些例子。

#3

Perhaps this may help. This is a modified version my my AGE function which returns Years, Months, Days, Hours, Minutes, and Seconds.

也许这可能有所帮助。这是我的AGE功能的修改版本，它返回年，月，日，小时，分钟和秒。

The TimeSpan function was scaled down to Days, Hours, Minutes, Seconds, and Milliseconds.

TimeSpan功能缩小为Days，Hours，Minutes，Seconds和Milliseconds。

It may look like overkill, but it is very accurate, and being a Single-Statement TVF, it is very fast.

它可能看起来有点矫枉过正，但它非常准确，而且作为单一陈述的TVF，它非常快。

Being a TVF, you can used as stand-alone, within a Join, or even a Cross Apply

作为TVF，您可以单独使用，在加入中使用，甚至可以使用Cross Apply

For Example:

例如：

Select * from [dbo].[udf-Date-TimeSpan] ('2016-07-29','2016-07-30 02:03:12.345')

Returns

返回

TimeSpan        Days  Hours Minutes Seconds Millisecond
1.02:03:12.348  1     2     3       12      348

The Function if Desired

所需的功能

ALTER FUNCTION [dbo].[udf-Date-TimeSpan] (@D1 DateTime,@D2 DateTime)
Returns Table
Return (
    with cteBN(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cteRN(R)   as (Select Row_Number() Over (Order By (Select NULL))-1 From cteBN a,cteBN b,cteBN c,cteBN d,cteBN e),  -- Max 100K Days or 273 Years
         cteDD(N,D) as (Select Max(R),Max(DateAdd(DD,R,@D1))From cteRN R Where DateAdd(DD,R,@D1)<=@D2),
         cteHH(N,D) as (Select Max(R),Max(DateAdd(HH,R,D))  From (Select Top 24 R From cteRN Order By 1) R, cteDD P Where DateAdd(HH,R,D)<=@D2),
         cteMI(N,D) as (Select Max(R),Max(DateAdd(MI,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteHH P Where DateAdd(MI,R,D)<=@D2),
         cteSS(N,D) as (Select Max(R),Max(DateAdd(SS,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteMI P Where DateAdd(SS,R,D)<=@D2),
         cteMS(N,D) as (Select Max(R),Max(DateAdd(MS,R,D))  From (Select Top 999 R From cteRN Order By 1) R, cteSS P Where DateAdd(MS,R,D)<=@D2)

    Select TimeSpan  = concat(cteDD.N,'.')+Format(cteHH.N,'00:')+Format(cteMI.N,'00:')+Format(cteSS.N,'00')+'.'+Format(cteMS.N-1,'000')
          ,[Days]    = cteDD.N
          ,[Hours]   = cteHH.N
          ,[Minutes] = cteMI.N
          ,[Seconds] = cteSS.N
          ,[Millisecond] = cteMS.N-1
     From  cteDD,cteHH,cteMI,cteSS,cteMS
)
--Select * from [dbo].[udf-Date-TimeSpan] ('2016-07-29','2016-07-30 02:03:12.345')

Edit - Perhaps a better illustration

编辑 - 也许是一个更好的例证

Declare @Table table (Date1 Datetime,Date2 DateTime)
Insert Into @Table values
('2016-01-01 00:00:00.200','2016-01-05 12:05:01.500'),
('2016-01-01 10:00:00.300','2016-01-05 12:30:30.500'),
('2016-01-01 10:00:00.800','2016-01-05 12:30:30.500')

Select A.*
      ,B.TimeSpan
 From @Table A
 Cross Apply [dbo].[udf-Date-TimeSpan] (A.Date1,A.Date2) B

Returns

返回

Date1                       Date2                       TimeSpan
2016-01-01 00:00:00.200     2016-01-05 12:05:01.500     4.12:05:01.300
2016-01-01 10:00:00.300     2016-01-05 12:30:30.500     4.02:30:30.200
2016-01-01 10:00:00.800     2016-01-05 12:30:30.500     4.02:30:29.700