Count WeekDays

Question

Count WeekDays

Matthew-12035

Say Hey Kid

Points: 685
More actions
February 19, 2003 at 7:20 am

#79770

I would like to create a SQL statement(s) that count up the number of weekdays in a certain date range.
SELECT @iNumDays = DATEDIFF(day, '01/01/2003', '02/01/2003')
will return 31 days, but that includes weekends. any way to not count weekend days?
Any help is appreciated, thanks for your time
Sincerely,
Matthew
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
http://www.embarcgroup.com

Matthew Mamet

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

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

well0549 SSCrazy Points: 2259 More actions · Answer 1

select datepart(dw,datefield) from tablexy where datefield ...

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

Matthew-12035 Say Hey Kid Points: 685 More actions · Answer 2

here's what i did, it seems to work correctly, tho i'm not overly happy about loops:

WHILE @pi_dStart <= @pi_dEnd

BEGIN

IF (DATEPART(dw,@pi_dStart) NOT IN (1,7))

SELECT @iNumDays = @iNumDays + 1

SELECT @pi_dStart = DATEADD(day, 1, @pi_dStart)

END

Sincerely,

Matthew Mamet

Web Developer

embarc LLC

http://www.embarcgroup.com

Matthew Mamet

NPeeters SSChampion Points: 12503 More actions · Answer 3

I guess you could find some kind of algorithm to subtract the number of weekend dates. In the end, there are 2 weekend days for each 7 days you count...

You will have to take start and end date type into account.

Something like

DATEDIFF(day, '01/01/2003', '02/01/2003') -

DATEDIFF(day, '01/01/2003', '02/01/2003')/7 + case datepart(dw, ENDDATE) WHEN 6 then 1 when 7 then 2 else 0 ...

And so on. I need to think about a complete and accurate solution, taking all possible combinations into account.

cql SSC Journeyman Points: 83 More actions · Answer 4

--try this

declare@WeekDays int,

@StartOnDate datetime,

@FinishOnDate datetime

select@StartOnDate = '04/05/2003',

@FinishOnDate = '04/28/2003'

select @WeekDays = DATEDIFF(day, @StartOnDate, @FinishOnDate)+1

- (1-abs(sign(1-datepart(weekday, @StartOnDate))))

- (1-abs(sign(7-datepart(weekday, @FinishOnDate))))

- DATEDIFF(week, @StartOnDate, @FinishOnDate)*2

-- result

select datename(weekday, @StartOnDate)StartOnDay, datename(weekday, @FinishOnDate)FinishOnDay, @WeekDays WeekDays