Need to pass a select to a variable to pick up last friday of 6 weeks ago

Question

hello I was looking to execute a stored procedure that receives variables for @startweek and @endweek.

I wanted to pass for execution that the @endweek to be the last friday of the current week and for the @startweek to be the friday of 6 weeks ago.

I have made the following for the last friday of current week:

 SELECT @startweek = '25 Jan 2019'
 SELECT @endweek =  DATE
FROM
  (SELECT DATE = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())) a
ORDER BY a.DATE DESC

What I'm needing to do is to get the way to pass the @endweek value (now is hardcoded) for it to select the last friday of 6 weeks ago.

one thing that I got to work is the following:

SELECT friday4WeeksAgo =dateadd(ww, -4,DATE)
FROM
  (SELECT DATE = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate())) a
ORDER BY a.DATE DESC

but I wasn't able to pass this to a variable

I have the procedure written, just need to figure out how to do to pass to these variable the values that I need (@startweek and @endweek) to show the last friday from 6 weeks ago and the last friday from current week

thank you for any help

sorry I can't reply directly, thank you for not flagging that I wasn't quite clear with my question. I have updated now and I'll remove this comment in a little time so it doesn't spam. thank you. — Baldie47, Aug 14 '19 at 14:49
you want to know how to call a procedure with these parameter ? Or do you need help in writing the procedure ? What is exact your problem/question ? — GuidoG, Aug 14 '19 at 14:53
I have the procedure written, just need to know how to get the last friday from 6 weeks ago and send it to the variable, I tried to clarify this now on the question — Baldie47, Aug 14 '19 at 14:58
https://stackoverflow.com/questions/11912181/get-last-fridays-date-unless-today-is-friday-using-t-sql How were you "not able to pass this to a variable"?? Did you try `SET @MyVariable = SELECT ... {your query}` ??? What did you try? — Tab Alleman, Aug 14 '19 at 17:09

score 1 · Accepted Answer · answered Aug 14 '19 at 14:58

1

Your calculation for last Friday is correct, but unnecessarily complicated.

SELECT @endweek = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate());
SELECT @startweek = DATEADD(WEEK, -6, @endweek)

EXEC YourProcedure
    @StartWeek = @startweek,
    @EndWeek = @endweek;

Is that what you were looking for?

answered Aug 14 '19 at 14:58

Eric Brandt

7,886
3
18
35

yes! this is exactly what I was needing, thank you very much for your help, I did started doing selects from tables and I believe that there is were I over complicated it. thank you! – Baldie47 Aug 14 '19 at 15:01

score 0 · Answer 2 · answered Aug 14 '19 at 15:12

0

This should work

DECLARE @startweek DATE,@endweek DATE
 SET @startweek = '14 Aug 2019'
 SET @endweek =  dateadd(day,case when DATEPART(DW,dateadd(ww,-6,@startweek))>5 then 6 else -1 end - 
                 DATEPART(DW,dateadd(ww,-6,@startweek)), dateadd(ww,-6,@startweek))

SELECT @startweek AS DatePassedIn,
       @endweek AS Six_weeks_ago_Friday_Date

--dateadd(ww,-6,@startweek)/*this part get 6 weeks back, based on date pass to @startweek*/

answered Aug 14 '19 at 15:12

JonWay

1,585
17
37

By incorporating `@@DATEFIRST` into his solution, the OP is avoiding a gotcha that can happen to calculations that rely on `DATEPART`. Your results could vary if the `DATEFIRST` setting changes in the environment. You may also want to consider [Bad Habits to Kick : Using shorthand with date/time operations](https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations). – Eric Brandt Aug 14 '19 at 16:42

Need to pass a select to a variable to pick up last friday of 6 weeks ago

2 Answers2