Say you need a date range on gathering data for a report starting from Friday to Thursday based on the current date.
Example dates July 5 to 11, July 12 to 18, July 19 to 25 and July 26 to Aug 1.
So for July 5 to 11, you want the report to run this range of dates if the current date is July 12 to 18. Next for July 12 to 18 the current dates would be July 19 to 25. So, for July 26 to Aug 1 would have the current dates of Aug 2 to Aug 8.
declare@date datetime
select@date =cast(CAST(getdate()asDATE)asdatetime)
SELECT@date =DATEADD(DAY,DATEDIFF(DAY,4,@date)/7 *7,5)
selectDATEADD(DAY,-3,DATEADD(WEEK,DATEDIFF(WEEK,0,@date),0))fri ,dateadd(day,6,DATEADD(DAY,-3,DATEADD(WEEK,DATEDIFF(WEEK,0,@date),0)))thu
,DATEADD(day,DATEDIFF(day,0,dateadd(day,6,DATEADD(DAY,-3,DATEADD(WEEK,DATEDIFF(WEEK,0,@date),0)))),’11:59:59′)
Sample output
— hard for demo
select@date =cast(CAST(‘2019-07-12 12:10:40.323’asDATE)asdatetime)
select@date =cast(CAST(‘2019-07-18 12:10:40.323’asDATE)asdatetime)
select@date =cast(CAST(‘2019-07-19 12:10:40.323’asDATE)asdatetime)
Notice this date is Aug 1 and it still get a date range of 7/19 to 7/25.
select@date =cast(CAST(‘2019-08-01 12:10:40.323’asDATE)asdatetime)
This date is Aug 2 get a date range of 7/28 to 8/1.
select@date =cast(CAST(‘2019-08-02 12:10:40.323’asDATE)asdatetime)
