SQL Server : get next relative day of week. (Next Monday, Tuesday, Wed.....)

1) Your solution uses a non-deterministic function: datepart(dw...) . Because of this aspect, changing DATEFIRST setting will gives different results. For example, you should try:

SET DATEFIRST 7;
your solution;

and then

SET DATEFIRST 1;
your solution;

2) Following solution is independent of DATEFIRST/LANGUAGE settings:

DECLARE @NextDayID INT  = 0 -- 0=Mon, 1=Tue, 2 = Wed, ..., 5=Sat, 6=Sun
SELECT DATEADD(DAY, (DATEDIFF(DAY, @NextDayID, GETDATE()) / 7) * 7 + 7, @NextDayID) AS NextDay

Result:

NextDay
-----------------------
2013-09-23 00:00:00.000

This solution is based on following property of DATETIME type:

• Day 0 = 19000101 = Mon

• Day 1 = 19000102 = Tue

• Day 2 = 19000103 = Wed

...

• Day 5 = 19000106 = Sat

• Day 6 = 19000107 = Sun

So, converting INT value 0 to DATETIME gives 19000101.

If you want to find the next Wednesday then you should start from day 2 (19000103/Wed), compute days between day 2 and current day (20130921; 41534 days), divide by 7 (in order to get number of full weeks; 5933 weeks), multiple by 7 (41531 fays; in order to get the number of days - full weeks between the first Wednesday/19000103 and the last Wednesday) and then add 7 days (one week; 41538 days; in order to get following Wednesday). Add this number (41538 days) to the starting date: 19000103.

Note: my current date is 20130921.

Edit #1:

DECLARE @NextDayID INT;
SET @NextDayID = 1; -- Next Sunday
SELECT DATEADD(DAY, (DATEDIFF(DAY, ((@NextDayID + 5) % 7), GETDATE()) / 7) * 7 + 7, ((@NextDayID + 5) % 7)) AS NextDay

Result:

NextDay
-----------------------
2013-09-29 00:00:00.000 

Note: my current date is 20130923.