Logic to calculate number of working days

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

Transact-SQL (2000)

Logic to calculate number of working days

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2005-07-14 : 10:46:13

I want to find the number of working days (Monday to Friday) between any two dates i.e., start_date and end_date (they be more than an year apart; for Eg. between 02/23/2004 and 09/15/2005). Can anyone help me with this? I would appreciate if I can get some sample code.

Thank you in advance.

PKS.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-14 : 10:57:12

Refer this
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx

Madhivanan

Failing to plan is Planning to fail

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-14 : 11:03:01

Look up DATEDIFF in BOL

Then in your WHERE clause filter out Saturday & Sunday (DATEPART)

Sample code:
Have a go at it yourself and if your struggling come back with what you have done so far and we will be able to help you out

Andy

Beauty is in the eyes of the beerholder

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-14 : 12:35:45

If you don't care about holidays, see if this works:

declare @startdate datetime,
@enddate datetime

set @startdate = convert(datetime, '02/23/2004')
set @enddate = convert(datetime, '09/15/2005')

SELECT
DATEDIFF(d, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate))
- DATEDIFF(wk, DATEADD(d, CASE WHEN DATEPART(dw, @startdate) = 7 THEN 1 ELSE 0 END, @startdate), DATEADD(d, CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END, @enddate)) * 2

sahu74
Posting Yak Master

100 Posts

Posted - 2005-07-14 : 13:01:38

Thank you for the directions. Its been very helpful so far.

I want to find the last date for the month of Begin_date and End_date. For Example, if the month of the begin_date (02-16-2004) is Feb, I would like to get the last date of the month as 29th. Is there a logic that I can use for this?

Any help will be appreciated.

Thank you.
PKS.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-14 : 14:57:27

SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,'02-16-2004')+1,0))

Andy

Beauty is in the eyes of the beerholder

sahu74
Posting Yak Master

100 Posts

Posted - 2005-07-14 : 17:50:23

Thank you all for all the help.

PKS.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-14 : 19:09:00

Another way for the last day of the month:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 30, begin_date), 30)