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
Begin
if Datepart(dw,@dt)=1
Begin
set @cnt=@cnt+1
set @dt=dateadd(dd,1,@dt)
End
set @dt=dateadd(dd,1,@dt)
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
FROM Master.dbo.SysComments sc1,
Master.dbo.SysComments sc2
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
Begin
if Datepart(dw,@dt)=1
Begin
set @cnt=@cnt+1
set @dt=dateadd(dd,6,@dt)
End
set @dt=dateadd(dd,1,@dt)
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.
Helpful Links:
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)
You must be logged in to reply to this topic. Login to reply