Get SQL Server DATEDIFF column as a TimeSpan when using SqlDataAdapter

Question

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

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.

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

You can cast to `time` if the difference is less than 24 hours. — Panagiotis Kanavos, Oct 11 '16 at 15:46
You can also get the TimeSpan value by subtracting `DateTime.MinValue` from the DateTime value — Panagiotis Kanavos, Oct 11 '16 at 15:47

Konstantin Ershov · Accepted Answer · 2016-10-11T16:00:57.367

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.

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.

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

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

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.

Check out this post for some examples.

edited May 23 '17 at 12:19

Community

1
1

answered Oct 11 '16 at 15:38

SlimsGhost

2,849
1
10
16

"as you read from the adapter" - that's what I'd like to do, but are there any hooks or callbacks or virtual methods in `SqlDataAdapter` that would allow me to do the conversions while reading? – sashoalm Oct 11 '16 at 15:41
I found about FillSchema from http://stackoverflow.com/a/9028126/492336, I think that would be the solution. – sashoalm Oct 11 '16 at 15:44
I think for the datatable, you have to have the non-TimeSpan column from the adapter, and then you can fill/add another TimeSpan column based off the db value – SlimsGhost Oct 11 '16 at 15:47
There is no need for conversion, just subtract DateTime.MinValue from the resulting DateTime column. This can be added as a calculated column to the datatable – Panagiotis Kanavos Oct 11 '16 at 15:48

John Cappelletti · Answer 3 · 2016-10-11T19:18:17.033

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

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

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

Being a TVF, you can used as stand-alone, within a Join, or even a 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

Get SQL Server DATEDIFF column as a TimeSpan when using SqlDataAdapter

3 Answers3