Sql: Get Next Relative Day of Week. (Next Monday, Tuesday, Wed.....)

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

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.

How to get next week monday to sunday in my sql

Add 6 days to get the sunday of the monday. Add 13 days to add the second sunday after the monday:

SELECT (now() + INTERVAL 7 - weekday(now()) DAY) + INTERVAL 6 DAY;

Find the next occurance of a day of the week in SQL

Here's some example SQL that I came up with. 3 iterations so you can follow how I got to the end. The 3rd iteration should be something you can incorporate into a WHERE clause by substituting your column names for the variables.

Setup:

DECLARE @Startdate DATETIME,@currentdate datetime
SET @Startdate = '1-26-2012' 
SET @Currentdate = '1-23-2012'

--This section just normalizes it so you can use 7 as the interval
--The offset depends on your current setting for DATEFIRST, U.S. English default is 7, Sunday.
-- see http://msdn.microsoft.com/en-us/library/ms187766.aspx 
DECLARE @StartDateWorkingDayOfWeek int,@CurrentDateWorkingDayOfWeek int
SELECT @StartDateWorkingDayOfWeek =(DATEPART(weekday,@Startdate)-2)
SELECT @CurrentDateWorkingDayOfWeek=(DATEPART(weekday,@Currentdate)-2)

Iteration #1

--Iteration 1 
IF @StartDateWorkingDayOfWeek < @CurrentDateWorkingDayOfWeek
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 7,@StartDateWorkingDayOfWeek)
else
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 0,@StartDateWorkingDayOfWeek)

Iteration #2

--Iteration 2
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 

CASE WHEN @StartDateWorkingDayOfWeek < @CurrentDateWorkingDayOfWeek
    then 7
    ELSE 0
    end

    ,@StartDateWorkingDayOfWeek)

Iteration #3

--iteration 3
SELECT DATEADD(DAY,DATEDIFF(DAY,0,@Currentdate)/7*7 + 

CASE WHEN (DATEPART(weekday,@Startdate)-2) < (DATEPART(weekday,@Currentdate)-2)
    then 7
    ELSE 0
    end

    ,(DATEPART(weekday,@Startdate)-2))

Hat tip to this article:
http://www.sqlmag.com/article/tsql3/datetime-calculations-part-3

SQL 'Round' Up a Date to a Given Day of the week

You can reduce that to one line of code that uses a little math, and some SQL Engine trivia.

The answers that depend on DATEPART return non-deterministic results, depending on the setting for DATEFIRST, which tells the SQL Engine what day of the week to treat as the first day of the week.

There's a way to do what you want without the risk of getting the wrong result based on a change to the DATEFIRST setting.

Inside SQL Server, day number 0 is January 1, 1900, which happens to have been a Monday. We've all used this little trick to strip the time off of GETDATE() by calculating the number of days since day 0 then adding that number to day 0 to get today's date at midnight:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()),0)

Similarly, day number 3 was January 4, 1900. That's relevant because that day was a Thursday. Applying a little math to the number of days, and relying on the DATEDIFF function to drop fractions, which it does, this calculation will always return the next Thursday for you:

SELECT DATEADD(DAY, (DATEDIFF(DAY, 3, GETDATE())/7)*7 + 7,3);

Credit to this answer for the assist.

So your final query comes down to this:

Select 
getdate() as duedate,
datepart(yy,getdate()) as duedate_yr,
datepart(ww,getdate()) as duedate_ww,
DATEADD(DAY, (DATEDIFF(DAY, 3, GETDATE())/7)*7 + 7,3) as duedate_Weekdue;

Previous Monday & previous Sunday's date based on today's date

Easy:

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

EDIT:

The below will handle the Sunday date issue.

DECLARE @input varchar(10)
--SET @input = '9/9/2012' -- simulates a Sunday
SET @input = GETDATE()

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 6)

Populate a field with the next Monday in April

As I believe that you have written a small mistake in your question :

Second example would be 2020-01-13 so the calculated date would need
to return 2010-04-6

You wanted to write :

Second example would be 2020-01-13 so the calculated date would need
to return 2020-04-6

Here is how you can do it:

SELECT Date_c
       , case when  Date_c >= DATEADD(DAY, (9 - DATEPART(dw,CONVERT(date,concat(year(Date_c), '-04-01')))), CONVERT(date,concat(year(Date_c), '-04-01')))
              then DATEADD(DAY, (9 - DATEPART(dw,CONVERT(date,concat(year(Date_c)+1, '-04-01')))), CONVERT(date,concat(year(Date_c)+1, '-04-01'))) 
              else DATEADD(DAY, (9 - DATEPART(dw,CONVERT(date,concat(year(Date_c), '-04-01')))), CONVERT(date,concat(year(Date_c), '-04-01')))
              end as NEXTMONDAY
from testT
where Date_c > '2003-03-31';

Here is the DEMO

Get the week start date and week end date from week number

You can find the day of week and do a date add on days to get the start and end dates..

DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]

DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) [WeekEnd]

You probably also want to look at stripping off the time from the date as well though.

---

Related Topics