# Number of Mondays between two dates

• Hey, I'm looking for t-sql that can take two dates and determine how many mondays are between them?

• I think this will give the number of Mondays between the two dates (both inclusive). 20060213 can be replaced by any Monday...

select (datediff(d, '20060213', @Date2) + 1)/7 - datediff(d, '20060213', @Date1)/7

• This is an interesting start, but not quite what I'm looking for.  In this solution you have to know the first Monday for any calculation.  I'm looking to take to dates and then calculate the number of mondays between them without having to know the first Monday in T-SQL, if possible.

• I don't quite understand what you're saying. As an example, to calculate the number of Mondays in the year 2000, do as follows:

select (datediff(d, '20060213', '20001231') + 1)/7 - datediff(d, '20060213', '20000101')/7

Could you give an example where my method returns something wrong?

• Hey, I appreciate your feedback.  I'm not saying that your solution is wrong.  What I'm looking for is a formula that would be inside a stored procedure.  Another stored procedure will be calling it and all this stored procedure would know would be the start date and the end date.  From that, I'd like to calculate the number of Mondays between the two dates.

Taffy

• Something like this?

create proc countMondays

(

@FromDate datetime,

@ToDate datetime,

@NumberOfMondays int output

)

as

select @NumberOfMondays = (datediff(d, '20060213', @ToDate) + 1)/7 - datediff(d, '20060213', @FromDate)/7

go

declare @count int

exec countMondays '20000101', '20001231', @count output

select @count

• For this i have created a function in which you have to pass two date from to to date

and it will calculate the no.of  mondays in the given two dates

Create Function Monday(@dt1 datetime,@dt2 datetime) returns int

as

Begin

Declare @cnt int,@dt as datetime

set @cnt=0

if @dt1 < @dt2

Begin

set @dt=@dt1

while @dt <= @dt2

Begin

if Datepart(dw,@dt)=1

Begin

set @cnt=@cnt+1

End

End

End

if @cnt=0

Begin

return 0

End

Return @cnt

End

select dbo.Monday('2005-01-01','2005-02-28')

ok bye??

• Note that the output of your function depends on the value of @@DATEFIRST:

declare @DateFirst int

select @DateFirst = @@DATEFIRST

set datefirst 1

select dbo.Monday('2006-02-13','2006-02-20')  --Returns 2

set datefirst 7

select dbo.Monday('2006-02-13','2006-02-20')  -- Returns 1 (incorrect)

set datefirst @DateFirst -- Resets @@DATEFIRST to original value

• This could be wrapped in either a function or stored procedure.  As noted above this also depends on the value of @@DATEFIRST.

--using a standard seq table

SELECT TOP 9999

IDENTITY(INT,0,1) AS N

INTO  #Seq

declare @startdate datetime

declare @enddate datetime

select @startdate = '13 Feb 2006',

@enddate = '20 Feb 2006'

select sum(case when datepart(dw, dateadd(dd, s.n, @startdate)) = 1 then 1 else 0 end)

from #seq s

where s.n <= datediff(dd, @startdate, @enddate)

drop table #SEQ

• Amit,

Nice function - I think one small change could also speed it up - since weeks are a predictable format, when you encounter a Monday skip ahead the next 6 days - the second dateadd will then add one more day - once you find a Monday, you only need to check the Mondays....

Create Function Monday(@dt1 datetime,@dt2 datetime) returns int

as

Begin

Declare @cnt int,@dt as datetime

set @cnt=0

if @dt1 < @dt2

Begin

set @dt=@dt1

while @dt <= @dt2

Begin

if Datepart(dw,@dt)=1

Begin

set @cnt=@cnt+1

End

End

End

if @cnt=0

Begin

return 0

End

Return @cnt

End

select dbo.Monday('2005-01-01','2005-02-28')

Regards,

Harley

• Run

select (@@Datefirst + Datepart(dw, YourDateField) - 2) %7 +1

FROM ..

with different Datefirst settings and see what you can get from it.

_____________
Code for TallyGenerator

• Jesper... I don't believe your formula works correctly...

select (datediff(d, '20060213', '02/26/2006') + 1)/7 - datediff(d, '20060213', '02/01/2006')/7
select (datediff(d, '20060213', '02/27/2006') + 1)/7 - datediff(d, '20060213', '02/01/2006')/7
select (datediff(d, '20060213', '02/28/2006') + 1)/7 - datediff(d, '20060213', '02/01/2006')/7

...The 27th is a Monday but they all return the same number of weeks.  Too bad because it's nasty fast.

I think the following works (finds inclusive Mondays)... I tested it some but I might have missed something...

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '02/01/2006'

SET @EndDate = '02/27/2006'

SELECT

DATEDIFF(wk,@StartDate,@EndDate)

-CASE WHEN DATENAME(dw,@EndDate)='Sunday' THEN 1 ELSE 0 END

+CASE WHEN DATENAME(dw,@StartDate)='Monday' THEN 1 ELSE 0 END

...lemme know, eh?

The "numbers" table and WHILE loop solutions are great but compared to a direct formula, I've found them to be a bit slow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff, your formula doesn't work if the start date is a Sunday - e.g if @startdate = @enddate which is a Sunday, it will return -1.

I believe the following works (but then again, I also believed this last time ). This time, I have tested it against Amit's function (although JeffB's solution must be faster) for all days in January 2006 (Amit's function can easily be fixed to work in the case startsate=enddate which is a Monday). Here it goes:

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

• OK.

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

gives you last Monday before @EndDate.

Datediff(dd, @StartDate, @EndDate - (@@Datefirst+Datepart(dw,@EndDate)-2) %7) / 7

gives you result.

The only thing you need to adjust is number of Mondays between 2 Mondays. Is it 0, 1 or 2? Add +1 or -1 to dates depending on you answer.

_____________
Code for TallyGenerator

• Sergiy, I don't believe your formula is correct if @StartDate=@EndDate='20051231'.

Jeff, I think this modification of your formula works:

DATEDIFF(wk,@startDate,@endDate)

-CASE WHEN DATENAME(dw,@endDate)='Sunday' THEN 1 ELSE 0 END

+CASE WHEN DATENAME(dw,@startDate)='Monday' THEN 1 ELSE 0 END

+CASE WHEN DATENAME(dw,@startDate)='Sunday' THEN 1 ELSE 0 END

Viewing 15 posts - 1 through 15 (of 35 total)