Difference between two dates. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Difference between two dates.

Author

Topic

qazxi
Starting Member

1 Post

Posted - 2005-03-14 : 04:20:58

Hi
I want to calculate the time difference between two dates but hours out of business hours must be excluded. Also beginning or end date could be out of business hours.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-14 : 06:03:49

This makes use of 2 "number tables", one for days, and one for hours.

declare @d1 datetime, @d2 datetime
select @d1 = dateadd(hh,-43,getdate()), @d2 = getdate()

select
	sum( case when hrs.number between 7 and 19 then 1 else 0 end ) as bzns_hrs
	,count(*) as tot_hrs
from
	master.dbo.spt_values dys cross join master.dbo.spt_values hrs
where
	dys.type = 'P' and hrs.type = 'P'
	and hrs.number between 0 and 23
	and dys.number <= datediff(day,@d1,@d2)
	and case when dys.number = 0 then hrs.number else 24 end >= datepart(hh,@d1)
	and case when dys.number = datediff(day,@d1,@d2) then hrs.number else 0 end <= datepart(hh,@d2)

-- this just displays the data used to sum the hours between @d1 and @d2
select
	@d1 as d1,@d2 as d2,dys.number,hrs.number
from
	master.dbo.spt_values dys cross join master.dbo.spt_values hrs
where
	dys.type = 'P' and hrs.type = 'P'
	and hrs.number between 0 and 23
	and dys.number <= datediff(day,@d1,@d2)
	and case when dys.number = 0 then hrs.number else 24 end >= datepart(hh,@d1)
	and case when dys.number = datediff(day,@d1,@d2) then hrs.number else 0 end <= datepart(hh,@d2)
order by 1,2,3,4

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-14 : 06:17:21

no numbers table


declare @Start datetime, @End datetime
select @Start = dateadd(n, 10, getdate()-3), @end = dateadd(n, 0, getdate())

select 	@Start, @End, 
(
	case when DATEPART (hour, @Start) between 8 and  15
	then 
		datediff(n, @Start, DATEADD(d, DATEDIFF(d, 0, @Start), 0) + '16:00:00') -- number of hours until 16:00
	else
		0
	end
+
	case when DATEPART (hour, @End) between 8 and 15
	then 
		datediff(n, DATEADD(d, DATEDIFF(d, 0, @End), 0) + '08:00:00', @End) -- number of hours after 08:00
	else
		0
	end
+
datediff (d, 
	DATEADD(d, DATEDIFF(d, case when DATEPART (hour, @End) between 8 and 15 then 0 else 1 end, @Start), 0), 
	DATEADD(d, DATEDIFF(d, 1, @End), 0))*8*60 -- number of working hours * minutes
)/60.00 as diff