Number of Mondays between two dates

Question

Post reply

Number of Mondays between two dates

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

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

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.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 3

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

Sergiy SSC Guru Points: 110208 More actions · Answer 4

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

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).

Sergiy SSC Guru Points: 110208 More actions · Answer 6

My query does not depend on DATEFIRST settings.

_____________
Code for TallyGenerator

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 7

Jesper-244176

SSCertifiable

Points: 7142

February 18, 2006 at 5:40 am

#621880

So I've noticed - neither does mine

Sergiy SSC Guru Points: 110208 More actions · Answer 8

What about server collation?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 10

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.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Sorry, I was looking on another your formula.

_____________
Code for TallyGenerator

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 12

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

g.fichtner Valued Member Points: 55 More actions · Answer 13

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>

====

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

thanks in advance for your help.

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

LerxtDBA SSCrazy Points: 2190 More actions · Answer 14

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

Offel Newbie Points: 1 More actions · Answer 15

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 ALL
SELECT CAST(DATEADD(day,1,date_) as date)
FROM cte
WHERE date_ < @endDate
)

SELECT sum(case when datepart(dw,date_) = 2 then 1 else 0 end) as nrOfMondays
FROM cte