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)

Wait! Before You GoWould you like to talk to a business software expert to answer your questions?

When you schedule a free consultation with MIBAR, you will experience a one-on-one conversation with a business technology expert who is passionate about understanding your unique needs or issues. Schedule a free consultation today at the link below or give us a call at (212) 869-9300.

Learn More
Open for Business – The Path to Recovery

This webinar is focused on helping leaders plan for the future and adapt their business model as the economy reopens.

Join MIBAR as we host guest panelist Gary Bettan, President of Broadfield Distributing Inc., Founder and President of Videoguys, and a digital transformation expert who has helped startups and mid-market leaders future-proof their business in an ever-changing competitive landscape.