Group: General Forum Members Last Login: Monday, September 24, 2012 2:01 PM Points: 31,Visits: 104

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.

Group: General Forum Members Last Login: Monday, September 24, 2012 2:01 PM Points: 31,Visits: 104

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.

Group: General Forum Members Last Login: Yesterday @ 1:26 AM Points: 225,Visits: 130

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

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

You cannot post new topics. You cannot post topic replies. You cannot post new polls. You cannot post replies to polls. You cannot edit your own topics. You cannot delete your own topics. You cannot edit other topics. You cannot delete other topics. You cannot edit your own posts. You cannot edit other posts. You cannot delete your own posts. You cannot delete other posts. You cannot post events. You cannot edit your own events. You cannot edit other events.

You cannot delete your own events. You cannot delete other events. You cannot send private messages. You cannot send emails. You may read topics. You cannot rate topics. You cannot vote within polls. You cannot upload attachments. You may download attachments. You cannot post HTML code. You cannot edit HTML code. You cannot post IFCode. You cannot post JavaScript. You cannot post EmotIcons. You cannot post or upload images.