# Number of Mondays between two dates

• Sergiy, I believe the following is correct:

select case when

Datediff(dd, @startDate, @endDate - (@@Datefirst+Datepart(dw,@endDate)-2) %7) < 0

then 0 else

Datediff(dd, @startDate, @endDate - (@@Datefirst+Datepart(dw,@endDate)-2) %7) / 7 + 1

end

The division and modulo operations behave strangely for negative numbers, I think. E.g

select (-1)/7

select (-1) % 7

is 0 and -1, respectively. I would expect it to be -1 and 6, respectively, and then your posted formula would work.

• There is no way to get (-1)%7 in my formula.

Both @@Datefirst and Datepart(dw,@endDate) are positive values, their SUM is >= 2.

And for both dtes '2005-12-31' it returns 0 which is right - no Mondays between Saturday 31/12/2005 and Saturday 31/12/2005.

But one thing I missed when copied script here:

@StartDate must be shifted to Previous Monday as well:

select Datediff(dd,

@StartDate - (@@Datefirst+Datepart(dw,@StartDate)-2) %7,

@EndDate - (@@Datefirst+Datepart(dw,@EndDate)-2) %7)

/ 7

_____________
Code for TallyGenerator

• What I meant was that you could easily get (-1)/7, although this wasn't clear from what I wrote.

I assumed you wanted to add 1 to the formula you stated perviously. Anyway, it seems to return 0 whenever the two dates are the same.

I think this is also the case with your new formula - you always get 0 when the two dates are the same

• I mean get (-1)%7, not get (-1)/7

And what you expect to get when 2 dates are the same?

There are no Mondays between them, so it must be 0.

What's wrong with that?

_____________
Code for TallyGenerator

• I see. No, nothing's wrong. You include the second date but not the first. I thought you included both dates (like I do in my query). I apologize, your query works perfectly.

But... I think my query is slightly faster (replace '19000102' by '19000101' to get the same output as your query).

• My query does not depend on DATEFIRST settings.

_____________
Code for TallyGenerator

• So I've noticed - neither does mine

_____________
Code for TallyGenerator

• And you formula returns 1 Monday between '2005-02-19' and '2006-02-20' and between '2006-02-20' and '2006-02-21'.

This Monday is included in both periods. Sounds not right.

_____________
Code for TallyGenerator

• My formula

datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

doesn't depend on datefirst settings and server collation. Like I have said before, it returns the number of Mondays between @dateFrom and @dateTo, both days inclusive. '2006-02-20' is a Monday, as far as I know, and therefore it will return 1 for both periods.

If you insist on not including the first date (but still including the second date), my formula is easily modified:

datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000101', @dateFrom)/7

I have tested that this formula has the same output as your formula for every single day of the year 2005 (365*365 test cases), so I guess both are correct. My formula, however, performs slightly better than your formula - at least, that was what I concluded in my test.

• Sorry, I was looking on another your formula.

_____________
Code for TallyGenerator

• Yes, the first one wasn't correct for dates after '20060213', as Jeff Moden pointed out. My present formula isn't correct for dates before '19000101', but I guess that's all right

• Hi - great posting and answers... I realize the posts are quite old - but hoping some of you experts are still available out here.

I used the counting method in crystal reports and it seems pretty good- but I am intrigued by the formula. But - when I try the formula I do not get the same results.

I am using the date range 2009-03-01 to 2009-03-31. Should have 5 Sundays, Mondays, Tuesdays and 4 of the rest of the week days.

When I use the formula

cnt:=datediff("d", date(1900,01,01), dt2)/7 - datediff("d", date(1900,01,02), dt1)/7

I show 4.43. Does your formula assume it needs to be rounded up? I tried to run it in my sql plus - but it did not like "DATEDIFF". I ran a modified version (just subtracting the dates) - and it also yielded a fractional number -

1 SELECT

2 (TO_DATE('03/31/2009','MM/DD/YYYY')-TO_DATE('01/01/1900','MM/DD/YYYY'))/7 -

3 (TO_DATE('03/01/2009','MM/DD/YYYY')-TO_DATE('01/01/1900','MM/DD/YYYY'))/7

4 datediff

5* FROM dual

SQL> /

DATEDIFF

----------

4.28571429

SQL>

SQL>

====

Am I missing something. Were the divisions supposed to be truncated maybe?

updated - tried with

cnt:=truncate(datediff("d", date(1900,01,01), dt2)/7) -

truncate(datediff("d", date(1900,01,02), dt1)/7)

and intitial testing looks like that may be working (o - i only tested one thing - hopefully this is right....)

Greg

• This might not be the most popular answer: a calendar table is terribly convenient to have around. I made one using the following structure:

CREATE TABLE [dbo].[Calendar](

[dt] [datetime] NOT NULL, --the actual date

[isWeekend] [bit] NOT NULL,

[isHoliday] [bit] NOT NULL,

[day_desc] [nvarchar](50) NULL, --a description for holidays "New Year's Day", "Thanksgiving Day", etc.

[Y] [smallint] NOT NULL, --year

[M] [tinyint] NOT NULL, --month

[D] [tinyint] NOT NULL, --day

[DW] [tinyint] NOT NULL, --day of week [1-7]

[JD] [smallint] NOT NULL, --Julian date [1-366]

[monthname] [varchar](9) NOT NULL,

[dayname] [varchar](9) NOT NULL, --translated day of week "Monday", "Tuesday", etc.

CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED

(

[dt] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

I then populated it out 50 years or so. I found a bunch of functions to define the holidays my company observes as well.

Once you have a table like this in place the solution to your challenge (and lots of other challenges) becomes trivial. I consider this the calendar equivalent of my numbers utility table. In fact, I used my numbers table to help me initially populate this calendar table. It makes life easy.

For me, the answer to "How many mondays between two dates?" looks like this:

`declare @startdate datetime`

`declare @enddate datetime`

`set @startdate = '01/01/2009'`

`set @enddate = '01/01/2010'`

`SELECT COUNT(*)`

`FROM dbo.calendar`

`WHERE dt >= @startdate`

` and dt <= @enddate`

` and DW = 2`

• A different approach is reccursion: (Im using T-sql)

`DECLARE @startDate date = '2019-04-01',@endDate date = '2019-05-01';WITH cte AS (SELECT @startDate as date_UNION ALLSELECT CAST(DATEADD(day,1,date_) as date)FROM cteWHERE date_ < @endDate)SELECT sum(case when datepart(dw,date_) = 2 then 1 else 0 end) as nrOfMondaysFROM cte`

Viewing 15 posts - 16 through 30 (of 35 total)