Exclude weekends from DateDiff

Question

Post reply

Exclude weekends from DateDiff

dhananjay.nagarkar

Default port

Points: 1496
More actions
April 9, 2013 at 10:16 am

#394550

Dear Friends,
I need an urgent help-
"How to Exclude Weekends from DateDiff function?"
Kind Regards
Dhananjay

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

To what granularity are you trying to exclude weekends?

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 2

Something like this might get you started?

DECLARE @d1 DATETIME,

@d2 DATETIME

SELECT @d1 = '04/01/2013',

@d2 = '04/09/2103'

SELECT Datediff(dd, @d1, @d2)

+ CASE WHEN Datepart(dw, @d1) = 7 THEN 1 ELSE 0 END

- (Datediff(wk, @d1, @d2) * 2 )

- CASE WHEN Datepart(dw, @d1) = 1 THEN 1 ELSE 0 END +

- CASE WHEN Datepart(dw, @d2) = 1 THEN 1 ELSE 0

END

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

Erin's code is pretty much the way that I'd do it. For an explanation of how it works, please see the following (my very first) article.

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

Erin used the numerical day of the week instead of the long version. You may have to pay a bit of attention to both versions if you're using a different language or if @@DateFirst is different than the default US-English settings.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

Hey Jeff?

I read your article and I know it's been a loooooooooooooooong time but you know you could replace this:

--===== If the inputs are in the wrong order, reverse them

IF @StartDate > @EndDate

SELECT @Swap = @EndDate,

@EndDate = @StartDate,

@StartDate = @Swap

with this:

--===== If the inputs are in the wrong order, reverse them

SELECT @Swap = @EndDate,

@EndDate = @StartDate,

@StartDate = @Swap

WHERE @StartDate > @EndDate

Just to stick with the set-based coding theme. 🙂

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 5

Erin Ramsay (4/9/2013)
Hey Jeff?
I read your article and I know it's been a loooooooooooooooong time but you know you could replace this:
--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
with this:
--===== If the inputs are in the wrong order, reverse them
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
WHERE @StartDate > @EndDate
Just to stick with the set-based coding theme. 🙂

Heh. You're right. It has been a long time and a lot of water has flowed under the proverbial bridge. 🙂 But, be careful... Control-of-flow statements, like IF, have nothing to do with whether or not something is set-based or not. They're just a part of "Divide'n'Conquer'.

Speaking of "set-based"...

Since I originally wrote that function for SQL Server 2000, which was before the advent of CTEs and Cross Apply (which is really just a correlated subquery), here's how I would write the function now for SQL Server 2000 to get away from the RBAR nature of Scalar Functions altogether. It still works just fine in 2005, as well.

CREATE FUNCTION dbo.WeekDayCount

(@pStartDate DATETIME, @pEndDate DATETIME)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

SELECT WeekDayCount =

(DATEDIFF(dd,d.StartDate,d.EndDate)+1) --Start with total number of days including weekends

- (DATEDIFF(wk,d.StartDate,d.EndDate)*2) --Subtact 2 days for each full weekend

- (1-SIGN(DATEDIFF(dd,6,d.StartDate)%7)) --If StartDate is a Sunday, Subtract 1

- (1-SIGN(DATEDIFF(dd,5,d.EndDate) %7)) --If EndDate is a Saturday, Subtract 1

FROM ( --=== Make sure the dates are in the correct order

SELECT StartDate = CASE WHEN @pStartDate <= @pEndDate THEN @pStartDate ELSE @pEndDate END

, EndDate = CASE WHEN @pStartDate <= @pEndDate THEN @pEndDate ELSE @pStartDate END

)d

;

Notice that the code is actually an iTVF (Inline Table Valued Function) which means it can only be used in a CROSS APPLY or a FROM clause. Since CROSS APPLY wasn't available back then, the function would be used like the mythical iSF (Inline Scalar Function) like this...

SELECT WeekDayCount = (SELECT WeekDayCount FROM dbo.WeekDayCount(StartDate,EndDate))

FROM dbo.SomeTable

;

Notice also that I've gotten away from the literal language dependency of "Saturday" and "Sunday".

iSFs are usually quite a bit faster than normal Scalar Functions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)