'SQL: get next relative day of week. (Next Monday, Tuesday, Wed.....)
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?
Solution 1:[1]
A calendar table is an alternative to using a bunch of date functions and date arithmetic. A minimal calendar table for this particular problem might look something like this.
2013-09-20 Fri
2012-09-21 Sat
2012-09-22 Sun
2012-09-23 Mon
2012-09-24 Tue
...
So a query to get the next Monday might look like this.
select min(cal_date)
from calendar
where cal_date > current_date
and day_of_week = 'Mon';
In practice, you'll probably want a lot more columns in the calendar table, because you'll find a lot of uses for it.
Also, code that uses a calendar table can usually be seen to be obviously correct. Reading the code above is simple: select the minimum calendar date that's after today and that falls on Monday. It's pretty rare to see code that relies on date functions and date arithmetic that's obviously correct.
Solution 2:[2]
It's an old question. But I'm sure that posting better solution worth it.
-- 0 = 1st Mon, 1 = 1st Tue, 2 = 1st Wed, ..., 5 = 1st Sat, 6 = 1st Sun
-- 7 = 2nd Mon, 8 = 2nd Tue, ...
declare @NextDayID int = 0, @Date date = getdate()
select cast (cast (
-- last Monday before [Date] inclusive, starting from 1900-01-01
datediff (day, @NextDayID % 7, @Date) / 7 * 7
-- shift on required number of days
+ @NextDayID + 7
as datetime) as date)
This solution is improved solution of @Bogdan Sahlean. It can operate @NextDayID that is greater than 6. So you can, for example, find 2nd Monday from today.
Following query shows that my solution works correctly.
select [Date]
, convert (char(5), [0], 10) as Mon1
, convert (char(5), [1], 10) as Tue1
, convert (char(5), [2], 10) as Wed1
, convert (char(5), [3], 10) as Thu1
, convert (char(5), [4], 10) as Fri1
, convert (char(5), [5], 10) as Sat1
, convert (char(5), [6], 10) as Sun1
, convert (char(5), [7], 10) as Mon2
, convert (char(5), [8], 10) as Tue2
from (
select [Date], NextDayID
, cast (cast (
datediff (day, NextDayID % 7, [Date]) / 7 * 7 -- last Monday before [Date] inclusive, starting from 1900-01-01
+ NextDayID + 7 -- shift on required number of days
as datetime) as date) as NextDay
from (
select datefromparts (2018, 5, dt) as [Date]
from (values(14),(15),(16),(17),(18),(19),(20))t_(dt)
) d
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8))nd(NextDayID)
) t
pivot (
min (NextDay) for NextDayID in ([0], [1], [2], [3], [4], [5], [6], [7], [8])
) pvt
Result:
Date | Mon1 | Tue1 | Wed1 | Thu1 | Fri1 | Sat1 | Sun1 | Mon2 | Tue2
-----------+-------+-------+-------+-------+-------+-------+-------+-------+------
2018-05-14 | 05-21 | 05-15 | 05-16 | 05-17 | 05-18 | 05-19 | 05-20 | 05-28 | 05-22
2018-05-15 | 05-21 | 05-22 | 05-16 | 05-17 | 05-18 | 05-19 | 05-20 | 05-28 | 05-29
2018-05-16 | 05-21 | 05-22 | 05-23 | 05-17 | 05-18 | 05-19 | 05-20 | 05-28 | 05-29
2018-05-17 | 05-21 | 05-22 | 05-23 | 05-24 | 05-18 | 05-19 | 05-20 | 05-28 | 05-29
2018-05-18 | 05-21 | 05-22 | 05-23 | 05-24 | 05-25 | 05-19 | 05-20 | 05-28 | 05-29
2018-05-19 | 05-21 | 05-22 | 05-23 | 05-24 | 05-25 | 05-26 | 05-20 | 05-28 | 05-29
2018-05-20 | 05-21 | 05-22 | 05-23 | 05-24 | 05-25 | 05-26 | 05-27 | 05-28 | 05-29
This solution doesn't depend on @@datefirst.
Solution 3:[3]
I think this is the best way of doing of finding the next Monday
CONVERT(VARCHAR(11),DateAdd(DAY,case
when (DateName(WEEKDAY, NextVisitDate) ='Tuesday') Then 6
when (DateName(WEEKDAY, NextVisitDate) ='Wednesday') Then 5
when (DateName(WEEKDAY, NextVisitDate) ='Thursday') Then 4
when (DateName(WEEKDAY, NextVisitDate) ='Friday') Then 3
when (DateName(WEEKDAY, NextVisitDate) ='Saturday') Then 2
when (DateName(WEEKDAY, NextVisitDate) ='Sunday') Then 1
else 0 end, DateAdd(DAY, DateDiff(DAY, 0, NextVisitDate), 0)),106) AS Monday,
Solution 4:[4]
Find the next upcoming day including today if today is that day which needs to be found out.
Just make a tweak... Set the variable @weekdayno as follows: 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday
DECLARE @weekdayno INT
DECLARE @todayno INT
SET @weekdayno = 2 ---For Monday----
SET @todayno = DATEPART(dw,GETDATE())
SELECT CASE
WHEN (@todayno = @weekdayno)
THEN CONVERT(varchar, GETDATE(), 101)
WHEN (@todayno < @weekdayno)
THEN CONVERT(varchar, (@weekdayno - @todayno + GETDATE()), 101)
WHEN (@todayno > @weekdayno)
then CONVERT(varchar,(GETDATE() - (@todayno - @weekdayno) + 7), 101)
END AS UpcomingOrToday
Solution 5:[5]
The following function enables to you generate the table on-the-fly...this is how I usually do it...I don't like the idea of a perm date table...seems unnecessary, but every person and situation are different :-)
CREATE function [dbo].[fxDateTable]
(
@begindate datetime = null
, @enddate datetime = null
)
RETURNS @dates TABLE
(
EventDate datetime primary key not null
)
as
begin
select @enddate = isnull(@enddate, getdate())
select @begindate = isnull(@begindate, dateadd(day, -3, @enddate))
insert @dates
select dateadd(day, number, @begindate)
from
(select distinct number from master.dbo.spt_values
where name is null
) n
where dateadd(day, number, @begindate) < @enddate
return
end
Solution 6:[6]
Try this: This will give the date for required weekday in a month.
declare @monthstartdate date='2020-01-01',@monthenddate date='2020-01-31',@weekday char(9)='thursday',@weeknum int=4
; with cte(N,WeekDayName_C,Date_C) as
(select 1,datename(WEEKDAY,@monthstartdate),@monthstartdate
union all
select n+1,datename(WEEKDAY,dateadd(day,n,@monthstartdate)),dateadd(day,n,@monthstartdate) from cte where n<31 and Date_C<=@monthenddate )
select * from (select *,ROW_NUMBER() over (partition by WeekDayName_C order by Date_C asc)Weeknum from cte)a
where WeekDayName_C=@weekday and Weeknum=@weeknum
Solution 7:[7]
I made this as a function, in which the procedure uses available streamlined knowledge thus it's, I think, a robust solution.
CREATE FUNCTION [nilnul.time_._dated.date].[NextWeekday]
(
@nextWeekDay int -- sunday as firstday is 1.
)
RETURNS datetime
AS
BEGIN
declare @time datetime;
set @time=getdate();
declare @weekday int;
set @weekday = datepart(weekday, @time) ;
declare @diff int;
set @diff= @nextWeekDay-@weekday;
--modulo 7 bijectively
declare @moduloed int;
set @moduloed = case
when @diff <=0 then @diff+7
else @diff
end;
return dateadd(day, @moduloed, @time);
END
Solution 8:[8]
I couldn't resist sharing my version. This is primitive for next business/weekday, but just change the "not in ('Saturday','Sunday')" part to be the day you are looking for inside the next week.
select top 1 [date]=convert(date, [date]),
, DayName = datename(dw, [date])
, Offset = [day]
from (
select [0]=getdate()
, [1]=getdate()+1
, [2]=getdate()+2
, [3]=getdate()+3
, [4]=getdate()+4
, [5]=getdate()+5
, [6]=getdate()+6) PVT
unpivot([date] for [day] in ([0],[1],[2],[3],[4],[5],[6])) as unpvt
where datename(dw,[date]) not in ('Saturday','Sunday') --wut day you lookin for?
and getdate() <> [date] --do you want today to be part of the results?
order by [date] asc
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Community |
| Solution 2 | |
| Solution 3 | |
| Solution 4 | Rhytech |
| Solution 5 | Ron Hudson |
| Solution 6 | Srikar mogaliraju |
| Solution 7 | NilNul |
| Solution 8 |