catalogue

introduce

background

So we need to be more complicated

Points of interest

A QA question needs "last week's data", which makes me think. Using the DATEADD function with a "WW" interval is easy, but it subtracts 7 days from today. This is different from "last week", because "last week" starts on Sunday and ends before midnight on the next Sunday. Or Saturday, if you're in Israel. Or Monday, if this is your company policy. So how do you solve this problem?

introduce

Weeks are a kind of pain - for us, weeks are natural units, but they are out of sync with anything else except the week itself: the first day of a month may be the beginning of the week, the second day, the third day,... Until the seventh day. But management - and humans - like weeks: our entire lives are organized around weeks, so we often want "sales in the last few weeks" or "delivery in the next few weeks". SQL does not handle this problem well. Trust me, this is actually a painful process because SQL BETWEEN is inclusive, but the DATETIME value is stored in Ticks that are not completely related to the number of days.

background

The obvious way to start is to use the WHERE clause with BETWEEN:

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

But this... Has a problem.

The first question is that this is relative to today: today is Tuesday, and now it is 11:40, so this applies to the same time last Tuesday and today. Not last week!

And... It's easy to make mistakes. Each time GETDATE is used, it will get the current time from the system, so two consecutive calls may return different results: different milliseconds, different hours, or even different centuries! Never call GETDATE multiple times: save its value and use it over and over again.

So we need to be more complicated

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

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

@Last week is - 1, and so on.

@NOW # get the current date and time, remove the time part, and save it as "today midnight".

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

@WS and @ WE are the beginning 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 the last week starting from today (September 14, 2021):

And:

... WHERE InsertDate BETWEEN @WS AND @WE

But this is not so simple, because SQL BETWEEN is inclusive: it returns all records between two dates, including the start date and the end date, and because SQL stores the date in Ticks that are not directly related to integer seconds or even microseconds, we can't just "subtract one" from the end date to get "everything is until midnight". I tried. Subtracting one millisecond gave me the same date and time:

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 will get "difference":

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 it was a hoax. I don't believe 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 Sunday, 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... Thoughtless in some ways

https://www.codeproject.com/Tips/5312808/Fetching-Data-from-a-Specific-Week-Relative-to-a-D