Fetching Data from a Specific Week Relative to a Date in SQL

Introduction

Weeks are a pain - they are natural units to us, but they aren't in sync with anything other than themselves: the 1^st of the month could be the start of the week, the second day, third, ... right up to seventh. But management - and humans as well - like weeks: we structure our whole lives around them, so we often want "last weeks sales" or "next weeks deliveries". And SQL isn't good any handling that. Believe me, it's actually a pain to deal with because SQL BETWEEN is inclusive, but DATETIME values are stored in Ticks which aren't exactly related to days.

Background

The obvious way to start is using a WHERE clause with BETWEEN:

,,, WHERE InsertDate BETWEEN DATEADD(ww, -1, GETDATE()) AND GETDATE()

but that ... has problems.

The first problem is that that is relative to today: it's a Tuesday, it's 11:40, so that works between last Tuesday at the same time, and today. Not last week!

And ... it's prone to error. Every time you use GETDATE, it gets the current time from the system, so two successive calls could return different results: different milliseconds, different hours, even different centuries! Never call GETDATE more than once: save its value and use that over and over again.

So We Need to Be a Little More Complex

First, let's make some variables to make life easier:

DECLARE @NOW DATETIME, @WS DATETIME, @WE DATETIME
DECLARE @WC INT
SET @WC = 1

@WC says how many weeks to offset by: 1 for last week, 0 for this week, -1 for next week, and so on.

@NOW gets the current date and time, strips out the time portion, and saves it as "today at midnight".

SET @NOW = CAST(GETDATE() AS DATE)

@WS and @WE are the start and end of the week respectively.

SET @WS = DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1), @NOW)
SET @WE = DATEADD(WW, 1, @WS)

So, if we ask for last week from today (2021-09-14) we get:

2021-09-05 00:00:00.000    

and:

2021-09-12 00:00:00.000

Which we could use in a WHERE directly:

... WHERE InsertDate BETWEEN @WS AND @WE

But it's not that simple, because SQL BETWEEN is inclusive: it returns all records between the two dates, including the start and end dates, and because SQL stores dates in Ticks which aren't directly related to integral seconds or even microseconds, we can't just "subtract one" from the end date to get "everything up to midnight". I've tried, and subtracting one millisecond gives me the same datetime:

SET @WE =  DATEADD(WW, 1, @WS)
SET @PD =  DATEADD(ms, -1, DATEADD(WW, 1, @WS))
SELECT @NOW, 
       @WS,
       @WE,
       @PD,
       CASE WHEN @PD = @WE THEN 'SAME' ELSE 'DIFFERENT' END

I get "SAME" every time ...

If I use -2, I get "DIFFERENT":

SET @WE =  DATEADD(WW, 1, @WS)
SET @PD =  DATEADD(ms, -2, DATEADD(WW, 1, @WS))
SELECT @NOW, 
       @WS,
       @WE,
       @PD,
       CASE WHEN @PD = @WE THEN 'SAME' ELSE 'DIFFERENT' END

But that's a bodge, and I don't trust it!

So, use this instead:

DECLARE @NOW DATETIME, @WS DATETIME, @WE DATETIME
DECLARE @WC INT
SET @WC = 1
SET @NOW = CAST(GETDATE() AS DATE)
SET @WS =  DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1), @NOW)
SET @WE =  DATEADD(WW, 1, @WS)

... WHERE InsertDate BETWEEN @WS AND @WE AND InsertDate != @WE

If your week doesn't start on a Sunday, then just offset @WS forward or backward appropriately:

-1 for Saturday:

SET @WS =  DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1) - 1, @NOW)

+1 for Monday:

SET @WS =  DATEADD(D, -1 * (@WC * 7 + DATEPART(dw, @NOW) - 1) + 1, @NOW)

Points of Interest

Only that SQL is ... um ... poorly thought out in some areas ...

History

• 14^th September, 2021: First version