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

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