# question

## How do I loop through a date range and count only the weekdays?

I tested with the following code which wasn't successful:

while (@datetest1 < @datetest2) begin if datepart(d,@datetest1) = 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 2 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 3 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 4 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 5 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 6 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 7 set @datetest1 = dateadd(d, 1, @datetest1) end

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

Best answer is to build a date table that gives you a column that increments for "business days" such that Friday, Saturday and Sunday all have the same value, and it only increments on the next Monday. Then you can also add in the holidays to that table, so it doesn't increment when there is a holiday present.

But, also, you're going about this wrong. Ignoring holidays, we know that there are five weekdays in each week, so first count the number of weeks, multiply by five. Then you have to figure out whether your start and end dates are on the same day of the week or not, fix that. You end up with something like this:

```DECLARE @startdate date, @enddate date;

SET @startdate = '1/1/2018';

SET @enddate = '1/31/2018';

SELECT datediff(ww,@startdate,@enddate) as weeks,datepart(dw,@startdate) as startdateWeekday,datepart(dw,@enddate) AS enddateWeekday;

SELECT datepart(dw,@startdate) - datepart(dw,@enddate) as offset;

SELECT (

datediff(ww,@startdate,@enddate)

*5

)--weekdays in the number of weeks between the dates

-

(--subtract the offset between the days of the week

datepart(dw,@startdate) - datepart(dw,@enddate)

);
```

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.