Finding the date for the next 4 Thursdays

[HalfDomeClmbr]

I am trying to figure out how to make a stored procedure that will output the date of the next four Thursdays. If it is any day of the week I need to find the first Thursday then the next three consecutive Thursdays.

I'm thinking that after finding the first Thursday I could do something like DATEADD(dw,5,@1stThursday).

Has anyone needed to do this and has some code.

Thanks,
John

 

[nikhilparchure]

use
Select datepart(dw , @Date)
Suppose its a Monday u will get 2 above
now do a 5-2 to get 3

Suppose its a Saturday u will get 7 above
now do a 5-7 to get - 2


Select dateadd(dd , 3 , @Date ) to get first thursday
Select dateadd(dd , -3 , @Date ) to get first thursday


Now Loop it Three more time adding 7
@count = 1
While @count = 3
Begin
Select @Date = dateadd(dd,7 , @Date )
Select @Date

end


pls convert this to a syntax

Regards
Nikhil

 

[HalfDomeClmbr]

Thanks nikhilparchure,

I created it a little differently and needed to output the four Thursdays in a month/day format. This is mainly what I've come up with.
I had to write the IF (@PubDay <= 5) formula to be sure it selected the next Thurdays and not the closest Thursday.


PRINT &quot;CREATE PROCEDURE Get4Thursdays&quot;

go

CREATE PROCEDURE Get4Thurdays
@StartDate char(10)
@AdNumber char(12)
AS

Declare @PubDay smallint
Declare @AddDays smallint
Declare @1stThursday datetime
Declare @2ndThursday datetime
Declare @3rdThursday datetime
Declare @4thThursday datetime
Declare @Schedule3 varchar(40)
Declare @1m char(2)
Declare @1d char(2)
Declare @2m char(2)
Declare @2d char(2)
Declare @3m char(2)
Declare @3d char(2)
Declare @4m char(2)
Declare @4d char(2)


select @PubDay = datepart(dw , @StartDate)
IF (@PubDay <= 5)
select @AddDays = 5 - @PubDay
ELSE
select @AddDays = 5 - @PubDay + 7
select @1stThursday = dateadd(dd, @AddDays, @StartDate)
select @2ndThursday = dateadd(dd, 7, @1stThursday)
select @3rdThursday = dateadd(dd, 14, @1stThursday)
select @4thThursday = dateadd(dd, 21, @1stThursday)

SELECT @1m = convert(char(2), datepart(mm, @1stThursday))
SELECT @1d = convert(char(2), datepart(dd, @1stThursday))
SELECT @2m = convert(char(2), datepart(mm, @2ndThursday))
SELECT @2d = convert(char(2), datepart(dd, @2ndThursday))
SELECT @3m = convert(char(2), datepart(mm, @3rdThursday))
SELECT @3d = convert(char(2), datepart(dd, @3rdThursday))
SELECT @4m = convert(char(2), datepart(mm, @4thThursday))
SELECT @4d = convert(char(2), datepart(dd, @4thThursday))

select @Schedule3 = @1m+'/'+@1d+','+@2m+'/'+@2d+','+@3m+'/'+@3d+','+@4m+'/'+@4d
select @Schedule3

 

[nikhilparchure]

Seems to me an extra usage of variables but if u are ok with it and the results are ok ... no complains

Regards
Nikhil