Calculate all "Sundays,Mondays...Saturdays" between two days in SQL Server

Question

I want to calculate total say Sundays,Mondays...Saturdays between two days. I want a do it in a select query because that is mandatory according to the situation where I'm currently working.

I have some working code where I'm able to calculate all sundays but not working in case of mondays

DECLARE  @StartDate DATE = '2011-10-01',
         @EndDate   DATE = '2011-10-31' 

SELECT DayCount = count(* )
FROM   (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
                        [Date] = dateadd(DAY,ROW_NUMBER()
                  OVER(ORDER BY c1.name),
                  DATEADD(DD,-1,@StartDate))
        FROM   [master].[dbo].[spt_values] c1 ) x
WHERE  datepart(dw,[Date]) = 1;

possible duplicate of [Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL](http://stackoverflow.com/questions/6684577/get-number-of-weekdays-sundays-mondays-tuesdays-between-two-dates-sql) — Ian Nelson, Dec 27 '11 at 09:35

score 4 · Accepted Answer · answered Dec 27 '11 at 09:49

I think your query delivers the correct result but could be simplified a bit.

It is however dependent on SET DATEFIRST setting.

datepart(dw,[Date]) = 1 will count the number of Mondays if SET DATEFIRST is 1.

Try this:

set datefirst 7 -- Sunday
select datepart(dw, '20111227')
set datefirst 1 -- Monday
select datepart(dw, '20111227')

Result:

-----------
3

-----------
2

Update: Another query that does the same.

select count(*) as Daycount
from master..spt_values as Number
where Number.type = 'P' and
      dateadd(day, Number.number, @StartDate) <= @EndDate and
      datepart(dw, dateadd(day, Number.number, @StartDate)) = 1

Super Mikael. Works like a charm. – Soham Dasgupta Dec 27 '11 at 10:17 — Soham Dasgupta, Dec 27 '11 at 10:17

score 3 · Answer 2 · answered Dec 27 '11 at 09:39

3

SELECT  dATE , COUNT(dATE)   FROM   (
                SELECT TOP (datediff(DAY,@StartDate,@EndDate) + 1 ) [Date] = DATENAME(dw , dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name),
                            DATEADD(DD,-1,@StartDate)))         
                            FROM   [master].[dbo].[spt_values] c1 ) X
                            GROUP BY Date

answered Dec 27 '11 at 09:39

dotnetstep

17,065
5
54
72

Your solution also works, but I'll go with Mikael Eriksson because I wanted columns wise answers. Anyway if I had the power to mark both answer correct I would do so today. Thanks. – Soham Dasgupta Dec 27 '11 at 10:17

score 2 · Answer 3 · edited Dec 10 '15 at 08:06

select
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 1 THEN 1 ELSE 0 END) Sunday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 2 THEN 1 ELSE 0 END) Monday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 3 THEN 1 ELSE 0 END) Tuesday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 4 THEN 1 ELSE 0 END) Wednesday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 5 THEN 1 ELSE 0 END) Thursday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 6 THEN 1 ELSE 0 END) Friday,
SUM(CASE WHEN datepart(dw, dateadd(day, Number.number, @fromDate)) = 7 THEN 1 ELSE 0 END) Saturday
from master..spt_values as Number
where Number.type = 'P' and dateadd(day, Number.number, @fromDate) <= @toDate

score 0 · Answer 4 · answered Dec 20 '12 at 02:45

Calculating a simple number of particular days between two dates should NOT require a numbers table. Forcing I/O into a simple date calculation is not good practice.

You can do a simple calculation by figuring out the number of weeks between the two dates, and adjusting for where the desired day falls in relation to the end periods.

But I want to present another way to do it that actually allows you to specify any pattern of days you want to count. For example, it is just as easy to calculate the number of Saturdays and Sundays at once as it is to calculate the number of Mondays. I took pains to also make this query entirely independent of the SET DATEFIRST setting.

DECLARE
   @StartDate date = '2011-10-01',
   @EndDate date = '2011-10-31';

WITH A AS (SELECT DayCount = DateDiff(day, @StartDate, @EndDate) + 1),
B AS (
   SELECT
      DayCount,
      WeekCount = DayCount + 6 / 7,
      Dow = DateDiff(day, '18991230', @StartDate) % 7 FROM A
)
SELECT
   MondayCount =
      Len(Replace(Substring(
         Replicate('0100000', WeekCount),
         Dow, DayCount
      ), '0', ''))
FROM B;

The trick is the string inside of the Replicate function. It encodes Monday by placing a 1 in the second position (starting with Sunday as the first day).

You may feel repelled by such a string-munching solution as this--but I have played with this before and expect that you will find it very difficult to improve on its speed with regular date math (especially the part about being able to calculate for multiple days at once).

Calculate all "Sundays,Mondays...Saturdays" between two days in SQL Server

4 Answers4

Linked