Получить столбец SQL Server DATEDIFF как TimeSpan при использовании SqlDataAdapter

Можно ли получить результат функции DATEDIFF в SQL Server как TimeSpan при заполнении DataTable с помощью SqlDataAdapter?

Как очень минимальный пример:

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

Это выводит System.Int32 вместо TimeSpan, и я не могу изменить DataType после того, как таблица уже заполнена table.Columns[0].DataType = typeof(TimeSpan);, потому что это вызовет исключение.

Я мог бы создать совершенно новую таблицу данных и скопировать в нее данные, но я бы не стал этого делать.


c# sql-server sqldataadapter
person sashoalm    schedule 11.10.2016    source источник
comment
Вы можете использовать time, если разница составляет менее 24 часов.   -  person Panagiotis Kanavos    schedule 11.10.2016
comment
Вы также можете получить значение TimeSpan, вычитая DateTime.MinValue из значения DateTime.   -  person Panagiotis Kanavos    schedule 11.10.2016

Ответы (3)


arrow_upward
2
arrow_downward

просто определите его перед выбором:

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

ИЗМЕНИТЬ

но будь осторожен. вы используете DATEDIFF с параметром mi. но когда вы сопоставляете foo с TimeSpan, это означает, что временной интервал будет создан с этим количеством минут, интерпретируемым как Ticks.

Итак, чтобы исправить это, вам нужно сделать что-то вроде этого

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

Так как тики составляют 100 наносекундных единиц.

но в большинстве случаев это приведет к SqlException: The datediff function resulted in an overflow.

person Konstantin Ershov    schedule 11.10.2016

arrow_upward
1
arrow_downward

SQL Server не имеет типа данных, который автоматически сопоставляется с .Net TimeSpan. Обычно вам нужно хранить диапазон как Int (или BigInt) и преобразовывать его в TimeSpan по мере чтения из адаптера.

Проверьте этот пост для некоторых примеров.

person SlimsGhost    schedule 11.10.2016
comment
как вы читаете из адаптера - это то, что я хотел бы сделать, но есть ли какие-либо хуки, обратные вызовы или виртуальные методы в SqlDataAdapter, которые позволили бы мне выполнять преобразования во время чтения? - person sashoalm; 11.10.2016
comment
Я нашел о FillSchema на stackoverflow.com/a/9028126/492336, я думаю, что это было бы решением. - person sashoalm; 11.10.2016
comment
Я думаю, что для таблицы данных у вас должен быть столбец, отличный от TimeSpan, из адаптера, а затем вы можете заполнить/добавить еще один столбец TimeSpan на основе значения db - person SlimsGhost; 11.10.2016
comment
Преобразование не требуется, просто вычтите DateTime.MinValue из результирующего столбца DateTime. Это можно добавить как вычисляемый столбец в таблицу данных. - person Panagiotis Kanavos; 11.10.2016

arrow_upward
1
arrow_downward

Возможно, это может помочь. Это модифицированная версия моей функции AGE, которая возвращает годы, месяцы, дни, часы, минуты и секунды.

Функция TimeSpan была уменьшена до дней, часов, минут, секунд и миллисекунд.

Это может показаться излишним, но это очень точно, и, поскольку TVF с одним утверждением, это очень быстро.

Будучи TVF, вы можете использовать его отдельно, в рамках соединения или даже перекрестного применения.

Например:

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

Возвращает

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

Функция при желании

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

Изменить. Возможно, лучшая иллюстрация

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

Возвращает

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
person John Cappelletti    schedule 11.10.2016