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?
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
.