0

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

Artemination
  • 703
  • 2
  • 10
  • 30
  • 1
    Can you give some more examples? Why doesn't `DateAdd(Day, 1, myDate)` work for you? – Dan Pichelman May 15 '13 at 16:22
  • Between Dateadd, and `datename(dw,myDate)` to return the name of the day of the week - It's really not that difficult. – Mike Gardner May 15 '13 at 16:29
  • Oh. You want to know when given a date/day-of-week, when the next matching date/day-of-week is? – Mike Gardner May 15 '13 at 19:24
  • yes, The next occurrence of a weekday, some thing like this at the final http://sqlmag.com/t-sql/datetime-calculations-part-3 – Artemination May 15 '13 at 19:33
  • Hi, If you are still interested, you might check this: http://stackoverflow.com/a/38601630/5089204. This answer provides a culture independent approach. – Shnugo Jul 28 '16 at 19:42

1 Answers1

0

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)
Dan Pichelman
  • 2,312
  • 2
  • 31
  • 42
  • Hi, for a culture independent approach you might check this: stackoverflow.com/a/38601630/5089204. – Shnugo Jul 28 '16 at 19:46