For Example, today is 18 Feb 2021 then the start date will be 14 Feb 2021 and the End Date will be 20 Feb 2021.
These ways for the last 3 weeks will be :
| week | start date | End Date |
|---|---|---|
| Week 1 | 14 Feb 2021 | 20 Feb 2021 |
| week 2 | 7 Feb 2021 | 13 Feb 2021 |
| week 3 | 31 Jan 2021 | 6 Feb 2021 |
Here week 1 is the current week, week 2 is the week before week 1 so on
I have tried the below script :
select cast(DATEADD(dd, -(DATEPART(dw, getdate())-1), getdate()) as date) [Week1 start] select cast(DATEADD(dd, 7-(DATEPART(dw, getdate())), getdate()) as date) [Week1 End] select cast(DATEADD(dd, -8-(DATEPART(dw, getdate())-2), getdate()) as date) [Week2 start] select cast(DATEADD(dd, -14-(DATEPART(dw, getdate())-2), getdate()) as date) [Week2 END]
which showing wrong output, can some help me in this, by pointing out where I am going wrong , or an alternative to getting the last 6-week start and end dates
Advertisement
Answer
Try storing the current week start / end in variables and then use DATEADD to calculate the previous weeks:
declare @weekStart date = cast(DATEADD(dd, -(DATEPART(dw, getdate())-1), getdate()) as date) declare @weekEnd date = cast(DATEADD(dd, 7-(DATEPART(dw, getdate())), getdate()) as date) select @weekStart, @weekEnd union all select DATEADD(dd, -7, @weekStart), DATEADD(dd, -7, @weekEnd) union all select DATEADD(dd, -7 * 2, @weekStart), DATEADD(dd, -7 * 2, @weekEnd)