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?

Bogdan Sahlean · Accepted Answer · 2013-09-23T18:41:00.357

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.

I like that solution better just wish I could use Sun as the first day of the week. Do you have a suggestion to do that? I can use Monday as the first day of the week if need be. — Josh, Sep 23 '13 at 15:10
Edit #1 it gets me the same results as mine, but I like it better because I don't have to use the non-deterministic function as you put it (I was unaware of that part of datepart/datefirst until now), and it allows me to keep the days in an easy to understand order for the end user. Thanks bogdan. — Josh, Sep 23 '13 at 22:02

score 2 · Answer 2 · edited May 23 '17 at 11:46

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.

A calendar table in PostgreSQL

Do you update that table yearly then or do you just fill it with 100 years worth of data? — Josh, Sep 20 '13 at 17:28
Fill it with 100 years worth of data. With 50 years of data, which is what I have on this machine, the query above runs in .050 ms. Good indexes are important. — Mike Sherrill 'Cat Recall', Sep 20 '13 at 18:03

GriGrim · Answer 3 · 2018-05-19T16:42:40.800

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.

Nathan Lincoln · Answer 4 · 2020-11-20T15:07:54.180

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,

score 1 · Answer 5 · answered Jun 17 '21 at 13:13

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

score 0 · Answer 6 · answered Apr 09 '15 at 21:18

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

score 0 · Answer 7 · answered Jan 21 '20 at 05:55

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

score 0 · Answer 8 · answered Sep 12 '20 at 12:13

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

to better this, it's planned that `bijective modulo' be a function as well — NilNul, Sep 12 '20 at 12:16

gt46l · Answer 9 · 2022-05-04T22:39:09.553

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

score 0 · Answer 10 · answered Apr 12 '23 at 15:10

Today I found this thread because a report I had to make, so I'll just share the way I did.

-- Monday two weeks from now
SELECT DATEADD(day,7*2+((DATEPART(weekday,GETDATE())-2)*-1),GETDATE());

-- Thursday three weeks from now
SELECT DATEADD(day,7*3+((DATEPART(weekday,GETDATE())-5)*-1),GETDATE());

-- Formula pattern
SELECT DATEADD(day,7*[NUMBER_OF_WEEKS]+((DATEPART(weekday,GETDATE())-[WEEKDAY_INT])*-1),GETDATE());

You can use a conditional in [NUMBER_OF_WEEKS] to check if the next weekday is in the current week or in the next one.

-- Next friday
SELECT DATEADD(day,7*IIF(DATEPART(weekday,GETDATE())>6,1,0)+((DATEPART(weekday,GETDATE())-6)*-1),GETDATE());

-- Formula pattern
SELECT DATEADD(day,7*IIF(DATEPART(weekday,GETDATE())>[WEEKDAY_INT],1,0)+((DATEPART(weekday,GETDATE())-[WEEKDAY_INT])*-1),GETDATE());

score 0 · Answer 11 · answered May 23 '23 at 00:53

I needed the week ending date with week ending Saturday. And if the current date is already a Saturday then the week ending date needs to be the same as the input date (NOT a week later!). This article showed me how. https://www.sqlservercentral.com/articles/how-to-find-the-start-and-end-dates-for-the-current-week-and-more

My code ended up like this to get the Saturday of the week of the pay date:

SELECT DATEADD(dd,7-DATEPART(dw, PayBusinessDate ),  PayBusinessDate )

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

11 Answers11

Linked