Struggeling to get date for Friday next week

Transact-SQL
1

Afternoon all,

As the title says really... I'm looking to compute the date for Friday next week. So basing the following calaculations on today / this week (Monday 24/06/2019 & Friday 28/06/2019)

This should give me date for monday next week:

SELECT
CONVERT(DATE, DATEADD(DAY, (DATEDIFF(DAY, 0, '2019-06-24') / 7) * 7 + 7, 0), 103) AS DateNextMonday

Which works absolutly fine, what ever day I run it this week I get the date for Monday next week.

However, getting the date for friday next week isn't proving quite so simple... By my calculations this should give me the date for Friday next week:

SELECT
    CONVERT(DATE, DATEADD(DAY, (DATEDIFF(DAY, 4, '2019-06-24') / 7) * 7 + 7, 4), 103) AS DateNextFriday

This works OK until its run on Friday, Saturday or Sunday this week:

SELECT
    CONVERT(DATE, DATEADD(DAY, (DATEDIFF(DAY, 4, '2019-06-28') / 7) * 7 + 7, 4), 103) AS DateNextFriday

It then gives the answer as Friday, week after next...

Have I made an error in my calculations, or am i missing a trick??

Thanks in advance...

Dave

2

I'm not sure , but here is a way :

declare @dt_getDate datetime = '20190627' --'getdate();

select datepart(WEEKDAY, @dt_getDate) as dayOfWeek
	,dateadd(day, - datepart(WEEKDAY, @dt_getDate), @dt_getDate) as lastSaturday
	,dateadd(day, 6 /* sunday to friday = 6*/, dateadd(day, - datepart(WEEKDAY, @dt_getDate), @dt_getDate)) as nextFridayAsDateTime
	,cast(dateadd(day, 6, dateadd(day, - datepart(WEEKDAY, @dt_getDate), @dt_getDate)) as date) as nextFridayAsDate
	,cast(dateadd(day, 6 + 7, dateadd(day, - datepart(WEEKDAY, @dt_getDate), @dt_getDate)) as date) as next2FridayAsDate
dayOfWeek lastSaturday nextFridayAsDateTime nextFridayAsDate next2FridayAsDate
5 2019-06-22 2019-06-28 2019-06-28 2019-07-05
3

Hey Stepson,

Perfect, that'll do nicely, thank you :smiley:

EDIT:

Errrr after a bit of further testing i'm not sure this works either.... If I just take :

SELECT 
cast(dateadd(day, 6, dateadd(day, - datepart(WEEKDAY, '2019-06-22'), '2019-06-22')) as date) as nextFridayAsDate

Then the result is 2019-06-21 which is the previous friday, likewise if its run on 2019-06-29 it gives a result of 2019/06/28.....

4

What date is the correct answer for each date you are calculating from? You never explicitly stated what the specific date result you want for each base date is.

5

Fair point Scott, I should have been more explicit.

For any given date it needs to return Friday's date next week. So if we use today & this week as the example

Saturday 22/06/2019 should return 28/06/2019
Sunday 23/06/2019 should return 28/06/2019

Monday 24/06/2019 should return 05/07/2019
Tuesday 25/06/2019 should return 05/07/2019
Wednesday 26/06/2019 should return 05/07/2019
Thursday 27/06/2019 should return 05/07/2019
Friday 28/06/2019 should return 05/07/2019
Saturday 29/06/2019 should return 05/07/2019
Sunday 30/06/2019 should return 05/07/2019

Saturday 01/07/2019 should return 12/06/2019
Sunday 02/07/2019 should return 12/06/2019
Monday 03/07/2019 should return 12/07/2019

Hope thats a bit clearer.... thanks

7

gotcha .. QuattroDave

working on it !!!

8

ahhhh i was just replying to your post and it changed LOL :smiley:

9

The idea I am working on

Get the week of date
Add 1 to it to get next week
Get Friday of that week

Example
Date '2019-06-07'
Date part week ..let's say 14
Add 1 to 14
15 next week
Get Friday of 15

10

hi QuattroDave

i got your answer
please verify

hope it helps
i love feedback thanks
:slight_smile: :slight_smile:

SQL ... 
DECLARE @date1  DATE = '2019-06-12' 
DECLARE @WeekNum  INT  =  Datepart (week, @date1) + 1 
DECLARE @YearNum CHAR (4) = Datepart (year, @date1) 

SELECT  Dateadd (wk,  Datediff (wk, 5, '1/1/' + @YearNum) + ( @WeekNum - 1 ), 4) AS 
FridayOfNextWeek
11 
DECLARE @date DATE = '20190622';
SELECT 
	Date = @Date, 
	NextFriday = DATEADD (day,11-DATEDIFF(DAY,'19000101',@date)%7,@date);
1 Like
12 
;with chimichanga
as
(
select DATEADD(day, object_id,  cast(getdate() as date)) ddd
   from sys.objects where object_id between 1 and 30 
   --order by 1
)

SELECT 
	Date = ddd, 
	NextFriday = DATEADD (day,11-DATEDIFF(DAY,'19000101',ddd)%7,ddd)
  from chimichanga
2 Likes