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

Question

What I need is a date for the next given day (Monday, Tuesday, Wed...) following today's date.

The user is allowed to select what day following they want and that is stored as an int in a table. "Call me next Tuesday (3)"

Sunday = 1
Monday = 2
Tuesday = 3
...

So my table looks like this.

UserID, NextDayID

What I have come up with is:

select dateadd(dd,(7 - datepart(dw,GETDATE()) + NextDayID ) % 7, getdate())

It seems to work and will return today's date if you ask for the next whatever day today is which I can add a week if needed.

What I am wondering is, is that a good solution or is there something that I'm missing?

Answer 1

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.