SQL function that given a date calculates the next date based on an day of week parameter

Question

i would like to know if someone have a function in SQL, than given a initial date and a day of week (tuesday for example) it can calculate when is the next date since de initial date that will be tuesday.

For example:

Given:

@initialdate=01-02-2013 --This day is Friday

@dayofweek = 3 --3 for Wednesday, 1 for Monday and 7 for Sunday

Return: date=06-02-2013

Maybe i have not explain well yet.. I mean A function that the parameter @dayofweek=3 (for wednesday) and then i can give many diferentes dates:

01-02-2013 then if @dayofweek=3 then next date will be 06-02-2013

02-02-2013 then if @dayofweek=3 then next date will be 06-02-2013

03-02-2013 then if @dayofweek=3 then next date will be 06-02-2013

04-02-2013 then if @dayofweek=3 then next date will be 06-02-2013

05-02-2013 then if @dayofweek=3 then next date will be 06-02-2013

06-02-2013 then if @dayofweek=3 then next date will be 13-02-2013

07-02-2013 then if @dayofweek=3 then next date will be 13-02-2013

Answer 1

This is ugly, but my attempts to produce a more elegant solution didn't work out so well.

Please note that this assumes that DATEPART(dw, @someVarThatIsSunday) returns 1. See Set DateFirst for more info.

declare @dayofweek int
set @dayofweek = 3
declare @initialDate datetime
set @initialDate = getdate()

declare @increment int

select @increment =
case DATEPART(dw, @initialDate)
    when 1 then @dayofweek
    when 2 then @dayofweek + 6
    when 3 then @dayofweek + 5
    when 4 then @dayofweek + 4
    when 5 then @dayofweek + 3
    when 6 then @dayofweek + 2
    when 7 then @dayofweek + 1
end

if @increment > 7
begin
    set @increment = @increment - 7
end

select DATEADD(day, @increment, @initialDate)