The other day we needed to write a report on online sales for the last week. SQL doesn’t offer developers many predefined functions to work with date ranges unlike the C# programming language. Here is a little example how to query SQL for some data between the dates for the last week.
By default the first day of the week is set to Sunday (default value 7), so if you need it to be Monday run the following command:
SET DATEFIRST 1
SQL Last Week Date Range
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
Now we can use above expressions in our query:
SELECT …. FROM… WHERE Sale_Date BETWEEN
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
Note that we had to convert dates in order to reset minutes/hours/seconds to 00:00:00, but it also means that if we want to include the whole Sunday into our week report we need to set the dates BETWEEN Monday AND Monday 00:00:00.