Featured Posts

WebOS 1.3.1 Update Fixes Google My Contacts syncing... Well folks, my NuevaSync Google Contacts syncing workaround has served me well since the Pre launched on June 6th.  I checked PreCentral.net before I went to bed and found...

Readmore

Palm Pre: Troubleshooting NuevaSync / Google Contacts Judging my my blog stats, my post on setting up your Palm Pre to Sync only the Google "My Contacts" group via Nuevasync is pretty popular.  I hope there are some people out...

Readmore

Palm Pre: Only Sync Google My Contacts Notice:  This is no longer necessary This process described below is no longer necessary. As of Web OS 1.3.1 (released mid-November 2009), WebOS devices automatically...

Readmore

AaronTerry.com Rss

T-SQL: Calculating number of weekdays between two dates

Posted on : 18-06-2009 | By : acterry | In : SQL

1

Tonight someone tweeted trying to find a ColdFusion or SQL solution to counting the number of weekdays (excluding Saturdays and Sundays) between two dates.  Most of the solutions online involving looping over the date range and adding to a counter if that iteration of the loop is a weekday.

That’s fine for limited, but probably not if you need to test dates that span many years or need to run the function 1,000s of times.  Anyway, here’s a SQL Server UDF solution.  It’s late, so hopefully the comments in the code suffice.  It should be straightforward to rewrite this in ColdFusion.

This is an improved version of the solution presented here.  That solution fails when the timespan is less than 7 days long and contains at least one weekend day.

CREATE FUNCTION [dbo].[dateDiffWeekdays] (
@startdaytime DATETIME;
@enddaytime DATETIME;
)
returns int
AS
begin
 
DECLARE @answer int;
SET @answer = 0
 
-- Strip Times
SELECT @start = dateadd(dd,0, datediff(dd,0,@startdaytime))
SELECT @end = dateadd(dd,0, datediff(dd,0,@enddaytime))
 
-- handle end conditions
DECLARE @firstWeekDayInRange datetime, @lastWeekDayInRange datetime;
SELECT @firstWeekDayInRange =
case
-- If Saturday, add two days
when datepart(dw,@start) = 7 then dateadd(day,2,@start)
-- If Sunday, add one day
when datepart(dw,@start) = 1 then dateadd(day,1,@start)
else @start
end
 
SELECT @lastWeekDayInRange =
case
-- If Saturday, substract one day
when datepart(dw,@end) = 7 then dateadd(day,-1,@end)
-- If Sunday, substract two days
when datepart(dw,@end) = 1 then dateadd(day,-2,@end)
else @end
end
 
-- add one day to answer (to count Friday) if enddate was on a weekend
IF @end != @lastWeekDayInRange
SET @answer = 1
 
SELECT @answer = @answer +
case
-- triggered if start and end date are on same weekend
when dateDiff(day,@firstWeekDayInRange,@lastWeekDayInRange) < 0 then (@answer * -1)
-- otherwise count the days and substract 2 days per weekend in between dates
else (DateDiff(day, @firstWeekDayInRange, @lastWeekDayInRange) - DateDiff(week, @firstWeekDayInRange, @lastWeekDayInRange)*2)
end
 
RETURN @answer
end
 
-- Call created function
SELECT dbo.dateDiffWeekdays('6/1/2009','6/13/2009')
Bookmark and Share

This website uses IntenseDebate comments, but they are not currently loaded because either your browser doesn't support JavaScript, or they didn't load fast enough.

Comments (1)

KonstantinMiller said on 06-07-2009

Hi. I like the way you write. Will you post some more articles?

Reply

Write a comment

Advertise Here