home

-- TSQL - Date arithmetic, average duration
Author Nigel Rivett
```
The requirement is to calculate the average duration from start and end datetimes
This discusses methods of obtaining this result and methods of performing arrithmetic on datetimes in general

--This will calculate the duration as a datetime for each entry
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'

;with dur as
(
select dur = datediff(ss,Time_Start, Time_end)
from @t
)
select hh = dur.dur/60/60, mm = dur.dur/60%60, ss = dur.dur%60
from dur

-- This might appear to work to give the average
;with dur as
(select dur = datediff(ss,Time_Start, Time_end)
from @t
)
select hh = avg(dur.dur)/60/60, mm = avg(dur.dur)/60%60, ss = avg(dur.dur)%60
from dur
/*
That's fine but it relies on the result of the datediff which gives an integer
*/
Try
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'Y'

;with dur as
(
select dur = datediff(ss,Time_Start, Time_end )
from @t
)
select dur.dur/60/60, dur.dur/60%60, dur.dur%60
from dur

Msg 535, Level 16, State 0, Line 33
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.

Try to use datediff with a less precise datepart.
Note: From v2016 there is a function datediff_big which will allow this to work

We can use the sql server datetime representation to perform this operation.
A datetime is held in two parts. The first part is the number of days from 19000101.
The second part is the time.
I like to think of this as a decimal number with the integer part being the number of days.
This can be used for arithmetic and is why addition of days works
select getdate()+2
is equivalent to

select convert(datetime,0.5)
gives 19000101 + 0.5 days - i.e midday on that day.

So we can transform a datetime to numeric datatypes. I usually use a float.
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'Y'

select Time_Start = convert(float,Time_Start), Time_end = convert(float,Time_end) from @t
select Time_Start = convert(datetime,convert(float,Time_Start)), Time_end = convert(datetime,convert(float,Time_end)) from @t

--Also we can subtract dates
select Time_end - Time_Start from @t

Armed with this knowledge we can easily calculate averages
We can also use the float datatype wherever a datetime is expected and it will be converted implicitly.

declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'Y'

;with dur as
(
select dur = Time_end - Time_Start
from @t
)
select convert(datetime,avg(convert(float,dur.dur)))
from dur

And we can exclude entries
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'N'

;with dur as
(
select dur = Time_end - Time_Start
from @t
where Include = 'Y'
)
select convert(datetime,avg(convert(float,dur.dur))), avg(convert(float,dur.dur))
from dur

-- and to format the result

declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '20000101 00:00:00', '20000102 10:05:13', 'Y'
insert @t select '18000101 00:00:00', '20000102 10:05:13', 'N'

;with dur as
(
select dur = Time_end - Time_Start
from @t
where Include = 'Y'
)
, dur_avg as
(
select	dur_d = convert(datetime,avg(convert(float,dur.dur)))
, dur_f = avg(convert(float,dur.dur))
from dur
)
select	  dur_d = dur_d
, dur_f = dur_f
, days_f = convert(int,dur_f)
, days_d = datediff(dd,0,dur_d)
, tot_hrs = convert(int,dur_f)*24 + datepart(hh,dur_f)
-- using date functions
, dd_d = datediff(dd,0,dur_d)		-- cannot use datepart as will have a limit of 31 days
, hh_d = datepart(hh,dur_d)
, mm_d = datepart(mi,dur_d)
, ss_d = datepart(ss,dur_d)
-- calculating from float - subtracting the higher parts
, dd_f = convert(int,dur_f)
, hh_f = convert(int,24*(dur_f - convert(int,dur_f)))
, mm_f = convert(int,
60*24*(dur_f - convert(int,dur_f))
- 60*convert(int,24*(dur_f - convert(int,dur_f)))
)
, ss_f = convert(int,
60*60*24*(dur_f - convert(int,dur_f))
- 60*convert(int,24*60*(dur_f - convert(int,dur_f)))
)
-- calculating from float - using modulus to remove higher parts
, dd_f2 = convert(int,dur_f)
, hh_f2 = convert(int,24*(dur_f - convert(int,dur_f)))
, mm_f2 = convert(int,60*24*(dur_f - convert(int,dur_f)))%60
, ss_f2 = convert(int,60*60*24*(dur_f - convert(int,dur_f)))%60
from dur_avg

```

home