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

Question

I need the date of the next day (Monday, Tuesday, Wed) following today's date.

The user has the right to choose which day after them he wants, and which is stored as an int in the table. "Call me next Tuesday (3)"

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

So my table looks like this.

 UserID, NextDayID 

What I came up with:

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

It seems that we are working and will return today's date, if you ask for the next, no matter what day it is today, I can add a week if necessary.

I am wondering if this is a good solution or is there something I am missing?

Answer 1

1) Your solution uses a non-deterministic function: datepart(dw...) . In this regard, a change in the DATEFIRST parameter will give different results. For example, you should try:

 SET DATEFIRST 7; your solution; 

and then

 SET DATEFIRST 1; your solution; 

2) The following solution is independent of the 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 the following property of type DATETIME :

• Day 0 = 19000101 = Mon

• Day 1 = 19000102 = Tue

• Day 2 = 19000103 = Wed

...

• Day 5 = 19000106 = Sat

• Day 6 = 19000107 = Sun

So, converting the value of INT 0 to DATETIME gives 19000101 .

If you want to find the next Wednesday , then you should start from the 2nd day ( 19000103 / Wed ), calculate the days between day 2 and the current day ( 20130921 ; 41534 days), divide by 7 (in order to get number of full weeks, 5933 weeks), several by 7 (41531 people 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 to get the next Wednesday ). Add this number (41538 days) to the start 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 .

Answer 2

A calendar table is an alternative to using a bunch of date functions and date arithmetic. The smallest 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 ... 

Thus, a request for 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 probably need a lot more columns in the calendar table, because you will find many possibilities for this.

In addition, code that uses a calendar table can usually be considered clearly correct. Reading the code above is simple: select the minimum calendar date, which is after today and which falls on Monday. Very rarely can one see code that relies on date functions and date arithmetic, which are obviously correct.

PostgreSQL calendar table

Answer 3

The following function allows you to generate a table "on the fly" ... so I usually do it ... I don’t like the idea of ​​a perm date table ... it seems unnecessary, but each person and situation is 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 

Answer 4

This is an old question. But I'm sure publishing the best solution is 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 an improved solution by @Bogdan Sahlean. It can run @NextDayID, which is greater than 6. So, for example, you can find the second Monday from today.

The following query shows that my solution is working 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 decision is independent of @@datefirst .

Answer 5

I think this is the best way to find 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,}