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

sql

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-09-30 : 09:56:54

Hi,
How do I use the datediff function to get the difference of two dates in days or hours but excluding weekends?
I would like to consider only 9-5 and mon-fri
Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 10:47:02

I guess the easiest way would be to have a schedule table...

And you left out holidays....

Do a search here on holidays...I know the solutions have been posted before....

Brett

8-)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-09-30 : 10:52:11

I googled and found a pertinent thread over at experts exchange. Here is the link http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20907090.html

A similar question was asked there, and many solutions were given. I liked this function the best because of the flexibility.

create function f1(@start datetime, @end datetime) returns @days table(x varchar(20)) as
 begin
   while @start <= @end begin
     insert into @days values(datename(dw, @start))
     set @start = DATEADD(d, 1, @start)
   end
   return
 end

Some examples:

-- How many weekend days between two dates
select count(*) from dbo.f1('1/1/2003', '1/30/2003') where x in ('Saturday', 'Sunday')

----------- 
8

(1 row(s) affected)

-- How many week days between to dates
select count(*) from dbo.f1('1/1/2003', '1/30/2003') where x not in ('Saturday', 'Sunday')

----------- 
22

(1 row(s) affected)

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-30 : 10:59:33

Very nice....still got the holiday problem...

Maybe you can blend the two....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31840

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-30 : 11:28:50

This is setbased, with no loops.

This assumes sunday is dayOfWeek #1 and saturday is dayOfWeek #7


Declare @date1 datetime,
	@date2 datetime

Select @date1 = '9/30/2004'
Select @date2 = '10/8/2004'

Select
	Days = datediff(dy,@date1,@date2) - ((datepart(dw,@date1)+datediff(dy,@date1,@date2))/7) - ((datepart(dw,@date1)+datediff(dy,@date1,@date2)-1)/7)

/*
	Select 
		date1=@date1,
		date2=@date2,
		(datepart(dw,@date1)+datediff(dy,@date1,@date2))/7 as Sat,
		(datepart(dw,@date1)+datediff(dy,@date1,@date2)-1)/7 as Sun
*/

doesn't handle holidays still

Corey

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-30 : 13:50:37

[code]--Create table with holidays
declare @holiday table (holiday datetime)
Insert into @holiday
Select '9/6/2004' union all
Select '10/11/2004' union all
Select '11/25/2004' union all
Select '12/31/2004'

--Select the holidays between dates
--and the difference between start and end dates
--asuming there are 5 working dates and deducting holidays
select holiday,
datediff(week,'11/18/2004','12/2/2004') * 5 -
datepart(dw,'11/18/2004') + datepart(dw,'12/2/2004') -
(Select Count(*)
from @holiday
Where holiday between '11/18/2004' and '12/2/2004')
as workingDays
from @holiday
Where holiday between '11/18/2004' and '12/2/2004'[/code]
Results[code]holiday workingDays
------------------------------------------------------ -----------
2004-11-25 00:00:00.000 9
[/code]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40735

Note: This will work only if start and end dates are weekdays (Mon-Fri)
*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources