Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

How To Calculate the Number of Week Days Between two Dates

If the start date and end date are both week days, then the total number of week days in between is simply:

(total difference in days) - (total difference in weeks) * 2

or
 DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2

... since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends.

If you have a table of holidays, then you can simply subtract them out as well:

DateDiff(dd, @start, @end) -
DateDiff(ww, @start, @end)*2 - 
(select count(*) from holidays where holiday_date between @start and @end)

Now, what if the start day or the end day is on a weekend?  In that case, you need to define what to do in those situations in your requirements.

For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st -- how many week days are between those dates? There's no universal correct answer; it could be 0, or 1, or perhaps even "undefined" (null) depending on your needs.

Print | posted on Thursday, July 31, 2008 12:39 PM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

I like your approach, but for my needs, I also need to include time in my calculation in addition to excluding weekends. If @End is 8/6 11am and @Start is 8/5 11:30am, I need a zero. I also have to include government holidays. So I think I'll be sticking with my lookup tables.
8/6/2008 6:44 PM | Wayne West
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

Yo uso la siguiente Instruccion:
(I use the next sentences:)

--- Declar the Dates
DECLARE @start As DateTime, @end As DateTime
--- Asign some values
SET @start=getDate()
SET @end=getDate()-25
--- Convert to DATE without time ( DD/MM/YYYY o regional format)

SELECT @start=CONVERT(datetime,LEFT(CONVERT(varchar,@start,120),10),120)
SELECT @end=CONVERT(datetime,LEFT(CONVERT(varchar,@end,120),10),120)

SELECT @start,@end
8/7/2008 8:53 AM | lucindom
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

Hi I need to to calculate number of months,weeks,quartyearly and half yearly between two dates in ms sql can any one help
9/16/2008 8:52 AM | Muthu
Gravatar

# re: How To Calculate the total Number of Days Between two Dates, asp.net

thanks, for ya patience
1/15/2009 3:32 AM | sumi
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

Hi,
I am using SQL script into MS Excel,
in stead of assigning value as below:
SET @start=getDate()
can I assign @start to refer to a cell in MS Excel worksheet?
Thanks...
1/30/2009 3:57 AM | Jan
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

I don't think this approach works with two dates on the weekends still. I tried it using the dates 3/1/08 as start date and 3/9/08 as end date and I get 3 days as my days.
2/6/2009 11:26 AM | ali rizwan
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

SELECT DATEDIFF(d,@StartDate,@EndDate)+1
- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )
- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End )
3/25/2009 1:59 AM | Sundar
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

etertretertret
5/18/2009 10:05 AM | ERTERTRE
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

Thanks for this it works a treat , saved us loads of brain ache.

Would it be too much to ask how you also count bank holidays?
6/18/2009 10:47 AM | Lizzy & Lorna
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

Lizzy & Lorna -- read the entire article, perhaps?
6/18/2009 11:46 AM | xyz
Gravatar

# how to calculate difference between two dates?

hai i need to calculate date differences in mysql
12/23/2009 1:28 AM | viji vijayan
Gravatar

# re: How To Calculate the Number of Week Days Between two Dates

hi. can you help me to know. how many saturday will be in jan 1-15. something like that.
1/7/2010 7:40 AM | ekhar
Gravatar

# Average number of transaction per week

hi friends,

how to calculate average number of transactions done per week and per month in mysql
8/13/2010 8:47 AM | mahendra
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET