问题描述:

This is harder than it looks. I need a function that calculates the numbers of a given weekday in a date range. I don't want any loops or recusive sql, there are millions of examples doing just that, I need a fast function for calculation.

Input of the function will be weekday, fromdata, todate

`-- counting fridays`

set datefirst 1

SELECT dbo.f_countweekdays(5, '2011-07-01', '2011-07-31'),

dbo.f_countweekdays(5, '2011-07-08', '2011-07-15'),

dbo.f_countweekdays(5, '2011-07-09', '2011-07-15'),

dbo.f_countweekdays(5, '2011-07-09', '2011-07-14')

expected result:

`5, 2, 1, 0`

I hope someone can help.

@Mikael Eriksson has got a wonderful idea, but his implementation seems a bit overcomplicated.

Here's what I've come up with (and I'd like to stress that **it is based on the solution by @Mikael**, to whom the main credit should go):

```
ALTER FUNCTION dbo.f_countweekdays (@Dow int, @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
RETURN (
SELECT
DATEDIFF(wk, @StartDate, @EndDate)
- CASE WHEN DATEPART(dw, @StartDate) > @Dow THEN 1 ELSE 0 END
- CASE WHEN DATEPART(dw, @EndDate) < @Dow THEN 1 ELSE 0 END
+ 1
)
END
```

**UPDATE**

As Mikael has correctly noted in his answer's comment thread, in order for the above solution to work correctly the DATEFIRST setting must be set to `7`

(Sunday). Although I couldn't find this documented, a quick test revealed that `DATEDIFF(wk)`

disregards the actual DATEFIRST setting and indeed returns the difference in weeks as if DATEFIRST was always set to 7. At the same time `DATEPART(dw)`

does respect `DATEFIRST`

, so with DATEFIRST set to a value other than 7 the two functions return mutually inconsistent results.

Therefore, the above script must be amended in order to account for different values of the DATEFIRST setting when calculating `DATEDIFF(wk)`

. Happily, the fix doesn't seem to have made the solution *much more* complicated than before, in my opinion. Judge for yourself, though:

```
ALTER FUNCTION dbo.f_countweekdays (@Dow int, @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
RETURN (
SELECT
DATEDIFF(wk, DATEADD(DAY, [email protected]@DATEFIRST, @StartDate),
DATEADD(DAY, [email protected]@DATEFIRST, @EndDate))
- CASE WHEN DATEPART(dw, @StartDate) > @Dow THEN 1 ELSE 0 END
- CASE WHEN DATEPART(dw, @EndDate) < @Dow THEN 1 ELSE 0 END
+ 1
)
END
```

^{Edited: both [email protected]@DATEFIRST % 7 entries have been simplified to just [email protected]@DATEFIRST, as someone suggested here.}

```
create function dbo.f_countweekdays
(
@DOW int,
@StartDate datetime,
@EndDate datetime
)
returns int
begin
return
( select datediff(wk, T2.St, T2.En) -
case when T1.SDOW > @DOW then 1 else 0 end -
case when T1.EDOW < @DOW then 1 else 0 end
from (select datepart(dw, @StartDate),
datepart(dw, @EndDate)) as T1(SDOW, EDOW)
cross apply (select dateadd(d, - T1.SDOW, @StartDate),
dateadd(d, 7 - T1.EDOW, @EndDate)) as T2(St, En))
end
```

An alternative approach is the good old-fashioned data warehouse time dimension, where you have a table with all potential dates in it, along with any useful information you want to filter/count by:

```
Key ActualDate DayName IsWeekday DayNumberInYear FinancialQuarter
20110101 1 Jan 2011 Saturday 0 1 2011 Q1
20110102 2 Jan 2011 Sunday 0 2 2011 Q1
20110103 3 Jan 2011 Monday 1 3 2011 Q1
```

Then just join to that table and filter, e.g.

```
SELECT
COUNT(*)
FROM
date_dimension
WHERE
ActualDate BETWEEN '1 Jan 2011' AND '3 Jan 2011' AND
IsWeekday = 1
```

If you do date analysis a lot over a *known range of dates*, this can really speed up and simplify your queries. Whether you know your possible date ranges in advance is the limiting factor on whether this is helpful, really, but it's a useful trick to know about.

This is what I came up with after trying alot of different approches. I did spend a long time on solving it and I was still working on it, when I posted the question. I decided to post it as an answer because of the self-learner badge, although I never got more than 2 points for an answer.

```
alter function dbo.f_countweekdays
( @day int, @fromdate datetime, @todate datetime )
returns int
begin
RETURN (SELECT datediff(day, @fromdate, dateadd(week,datediff(week,0,@todate - 1) +
CASE WHEN datepart(weekday,@todate) < @day THEN 0 ELSE 1 END,0) + @day - 1) / 7)
end
```