future days

[Technokrat]

Is there a way in a single select statement to return the Tuesdays for the next future (N) weeks from the current date?

 

[RyanEK]

You could create a UDF that accepts a date period and DOW. The function can iterate through each day and return the valid days.

 

[PWise]

Create a Numbers table

CREATE TABLE [Digits] (
    [DigitID] [int] NOT NULL ,
    CONSTRAINT [PK_Digits] PRIMARY KEY  CLUSTERED 
    (
        [DigitID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO
DECLARE @position int
SET @position = 1
WHILE @position <= 1000
BEGIN
   insert into Digits(DigitID) values (@position)
   SET @position = @position + 1
END
go

Create procedure NextWhatDay

@Dayofweek int,
@week int


As

SELECT     DATEADD(d, DigitID, GETDATE()) AS Dayofweek
FROM         Digits
WHERE     (DATEPART(dw, DATEADD(d, DigitID, GETDATE())) = @Dayofweek) 
AND (DATEADD(d, DigitID, GETDATE()) <= DATEADD(wk, @week, GETDATE()))

Return

NextWhatDay 3,7 --for next 7 tusdays
NextWhatDay 2,100 --for next 100 mondays