Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Number of Mondays between two dates Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, February 16, 2006 4:36 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, June 1, 2016 3:40 PM Points: 31, Visits: 107
 Hey, I'm looking for t-sql that can take two dates and determine how many mondays are between them?
Post #258968
 Posted Thursday, February 16, 2006 4:57 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 17, 2011 6:28 AM Points: 422, 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
Post #258979
 Posted Thursday, February 16, 2006 5:17 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, June 1, 2016 3:40 PM Points: 31, 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.
Post #258987
 Posted Thursday, February 16, 2006 5:23 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 17, 2011 6:28 AM Points: 422, 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')/7Could you give an example where my method returns something wrong?
Post #258990
 Posted Thursday, February 16, 2006 5:40 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, June 1, 2016 3:40 PM Points: 31, 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
Post #258999
 Posted Thursday, February 16, 2006 5:47 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 17, 2011 6:28 AM Points: 422, 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)/7godeclare @count intexec countMondays '20000101', '20001231', @count outputselect @count
Post #259002
 Posted Thursday, February 16, 2006 6:28 AM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, September 16, 2015 3:26 AM Points: 228, Visits: 147
 For this i have created a function in which you have to pass two date from to to dateand it will calculate the no.of  mondays in the given two dates  Create Function Monday(@dt1 datetime,@dt2 datetime) returns intasBeginDeclare @cnt int,@dt as datetimeset @cnt=0if @dt1 < @dt2Begin 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) EndEndif @cnt=0Begin  return 0EndReturn @cntEndselect dbo.Monday('2005-01-01','2005-02-28') ok bye??
Post #259011
 Posted Thursday, February 16, 2006 6:43 AM
 SSC-Addicted Group: General Forum Members Last Login: Friday, June 17, 2011 6:28 AM Points: 422, Visits: 33
 Note that the output of your function depends on the value of @@DATEFIRST:declare @DateFirst intselect @DateFirst = @@DATEFIRSTset datefirst 1select dbo.Monday('2006-02-13','2006-02-20')  --Returns 2set datefirst 7select dbo.Monday('2006-02-13','2006-02-20')  -- Returns 1 (incorrect)set datefirst @DateFirst -- Resets @@DATEFIRST to original value
Post #259017
 Posted Thursday, February 16, 2006 10:34 AM
 SSC Veteran Group: General Forum Members Last Login: Friday, April 25, 2014 8:34 AM Points: 255, 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 sc2declare @startdate datetimedeclare @enddate datetimeselect @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 swhere s.n <= datediff(dd, @startdate, @enddate)drop table #SEQ
Post #259126
 Posted Thursday, February 16, 2006 12:51 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, October 28, 2008 5:46 PM Points: 61, 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 intasBeginDeclare @cnt int,@dt as datetimeset @cnt=0if @dt1 < @dt2Begin 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) EndEndif @cnt=0Begin  return 0EndReturn @cntEndselect dbo.Monday('2005-01-01','2005-02-28') Regards,Harley
Post #259173

 Permissions