Script to filter by most recent end of week date

Question

My company's reporting week is Monday through Sunday. On Monday morning's I run several queries using Microsoft SQL Server Management Studio to report on business activity for the previous week. I currently use declaration statements to pull the desired date range. This works quite well as long as I'm running reports on Monday. However, if Monday was a holiday and I'm not running reports until Tuesday, I need to manually modify my date range for each query. How can I modify my date filter to retrieve records through the previous "Sunday" so it doesn't matter what day I actually run the report.

Here's a sample query;

Declare @DATEFROM SMALLDATETIME = (CONVERT(datetime, getdate() + cast('00:00' as datetime)) - 8), @DATETO smalldatetime = (CONVERT(datetime, getdate() + cast('23:59' as datetime))-2);

Create Table #SALES ([PartNumber] CHAR(5), [DateSold] SMALLDATETIME)

Insert Into #SALES   Select   '10190',  '6/3/2018 11:00'
Insert Into #SALES   Select   '10213',  '6/8/2018  8:00:00 AM'
Insert Into #SALES   Select   '10214',  '6/5/2018  9:30:00 AM'
Insert Into #SALES   Select   '10215',  '6/4/2018  1:00:00 PM'
Insert Into #SALES   Select   '10217',  '6/6/2018  1:00:00 PM'
Insert Into #SALES   Select   '10219',  '6/7/2018  12:00:00 PM'
Insert Into #SALES   Select   '10220',  '6/9/2018  3:30:00 PM'

Insert Into #SALES   Select   '10221',  '6/11/2018  8:30:00 AM'
Insert Into #SALES   Select   '10222',  '6/11/2018  2:30:00 PM'
Insert Into #SALES   Select   '10225',  '6/8/2018  8:00:00 AM'
Insert Into #SALES   Select   '10227',  '6/10/2018  9:00:00 AM'
Insert Into #SALES   Select   '10231',  '6/10/2018  1:00:00 PM'
Insert Into #SALES   Select   '10233',  '6/2/2018  8:00:00 AM';

SELECT S.PartNumber, S.DateSold 
FROM #SALES S 
WHERE DATESOLD BETWEEN @DATEFROM AND @DATETO 
ORDER BY DateSold;

DROP TABLE #SALES

Answer 1

First, a few items of interest:

• SQL Server's SET DATEFIRST command allows you to choose any weekday as the first day of the week, from 1 (Monday) through 7 (Sunday).
• You can query the current value of this setting using the expression @@datefirst.
• DATEPART(weekday, getdate()) will return a number for the current weekday where 1 indicates the day set by SET DATEFIRST, 2 indicates the day after, etc.

So suppose I want to answer the question: how many days prior to some arbitrary date @TestDate was the most recent Monday? The number of days that have passed since the most recent beginning of a week is DATEPART(weekday, @TestDate) - 1, and the number of days that pass between a Monday and the beginning of a week is @@datefirst - 1, so the number of days that have passed since the most recent Monday is the sum of those quantities modulo 7:

declare @TestDate date = convert(date, getdate());
declare @DaysPastMonday int = (@@datefirst + datepart(weekday, @TestDate) - 2) % 7;

With this information you can get your date range very easily:

declare @DateTo date = dateadd(day, -@DaysPastMonday, @TestDate);
declare @DateFrom date = dateadd(day, -7, @DateTo);

But note that both of the dates I've selected here are Mondays, whereas you want a Monday through a Sunday. The reason I've done it this way is that if you're going to be looking at fields that potentially have a time component as well as a date (e.g., datetime or datetime2), and you want to use BETWEEN, then you need to make sure that your end date has the latest time of day that SQL Server is capable of representing. I find it cleaner to use a date range that's inclusive on the start date and exclusive on the end date. So in place of an expression like this:

x BETWEEN @DateFrom AND @DateTo

You'd write your queries to use an expression like this:

x >= @DateFrom AND x < @DateTo

Answer 2

Declare @DATEFROM datetime = DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0), 
@DATETO datetime = DATEADD(ms, -3, (select DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 7)));