SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Number of Mondays between two dates


Number of Mondays between two dates

Author
Message
TaffyLewis
TaffyLewis
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 107
Hey, I'm looking for t-sql that can take two dates and determine how many mondays are between them?



Jesper-244176
Jesper-244176
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 33

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


TaffyLewis
TaffyLewis
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 107
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.



Jesper-244176
Jesper-244176
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 33

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?


TaffyLewis
TaffyLewis
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 107

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





Jesper-244176
Jesper-244176
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 33

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


AMIT GUPTA-263376
AMIT GUPTA-263376
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2412 Visits: 147

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
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??


Jesper-244176
Jesper-244176
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3824 Visits: 33

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



JeffB
JeffB
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 74

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


Harley Arnett-275660
Harley Arnett-275660
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 371

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search