Finding no of weekend days between two dates

Question

Finding no of weekend days between two dates

Srinivas Arikatota

Grasshopper

Points: 17
More actions
May 11, 2005 at 5:45 am

#67327

Could you help me on how to get the no of weekenddays within 2 given dates.
Thanks in anticipation

Viewing 8 posts - 1 through 7 (of 7 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

See if this helps: http://www.aspfaq.com/show.asp?id=2519

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 2

SET DATEFIRST 7

SELECT ((DATEDIFF(week,[startdate],[enddate]) -

(CASE WHEN DATEPART(weekday,[startdate]) IN (1,7)

OR DATEPART(weekday,[enddate]) IN (1,7) THEN 1 ELSE 0 END)) * 2) +

(CASE WHEN DATEPART(weekday,[startdate]) = 7 THEN 1 ELSE 0 END) +

(CASE WHEN DATEPART(weekday,[enddate]) = 1 THEN 1 ELSE 0 END)

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

As I've said, use a calendar table.

Hi Dave,

long time no see!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 4

As I've said, use a calendar table.

Maybe, maybe not, a lot of trouble for small return

All depends on how and how often it is required

long time no see!

Yeah, seems like a long time.

I notice that your posts seem to be a lot of references

Fed up of writing your own answers

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Nah, that way I can increase postcount even more quickly

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

steve goluchowski SSC Veteran Points: 280 More actions · Answer 6

Here's a way that doesn't use a calendar table. It counts the # of weekdays though...

DECLARE @dateStart DATETIME

DECLARE @dateEnd DATETIME

SET @dateStart = '2/1/2004'

SET @dateEnd = '2/29/2004'

--adjust start weekend date to Monday

IF DATEPART(dw,@dateStart) = 7

SET @dateStart = @dateStart + 2

IF DATEPART(dw,@dateStart) = 1

SET @dateStart = @dateStart + 1

--adjust end weekend day to Friday

IF DATEPART(dw,@dateEnd) = 7

SET @dateEnd = @dateEnd - 1

IF DATEPART(dw,@dateEnd) = 1

SET @dateEnd = @dateEnd - 2

SELECT DATEDIFF(dd, @dateStart, @dateEnd) / 7 * 5

+

CASE

WHEN

DATEDIFF(dd, @dateStart, @dateEnd) % 7 = 6

THEN 5

WHEN

DATEPART(dw, @dateStart) > DATEPART(dw, @dateEnd)

THEN DATEDIFF(dd, @dateStart, @dateEnd) % 7 - 1

ELSE DATEDIFF(dd, @dateStart, @dateEnd) % 7 + 1

END

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

This'll work if you have a DBA that doesn't understand the merits of a date table and it's short enough to use in inline code if the DBA won't let you build a UDF, either... it relies on the fact that "wk" is not a 7 day counter... "wk" only increments in value when the day of the week changes from Saturday to Sunday.... that only happens on whole weekends which is why the *2...

SELECT DATEDIFF(wk,@StartDate,@EndDate)*2

+ CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END

+ CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END

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