Click here to Skip to main content
15,035,866 members
Articles / Programming Languages / SQL
Tip/Trick
Posted 14 Sep 2021

Tagged as

Stats

2.2K views
2 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
14 Sep 2021CPOL2 min read
How do you get last weeks data only? It's a little more complex than you might have thought
A QA question wanted "the last week of data" and it got me thinking. It's easy to use the DATEADD function with the "WW" interval, but that subtracts seven days from today. That's not the same as "last week" because "last week" started on a Sunday, and ended just before midnight on the following Sunday. Or Saturday, if you are in Israel. Or Monday if that is your company rule. So how do you work that out?

Introduction

Weeks are a pain - they are natural units to us, but they aren't in sync with anything other than themselves: the 1st 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:

SQL
,,, 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:

SQL
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".

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

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

SQL
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:

SQL
... 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:

SQL
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":

SQL
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:

SQL
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:

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

+1 for Monday:

SQL
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

  • 14th September, 2021: First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

Comments and Discussions

 
GeneralMy vote of 5 Pin
chessdr15-Sep-21 9:05
Memberchessdr15-Sep-21 9:05 
QuestionWhat about datediff? Pin
Malte Klena15-Sep-21 4:08
MemberMalte Klena15-Sep-21 4:08 
AnswerRe: What about datediff? Pin
OriginalGriff15-Sep-21 4:41
mveOriginalGriff15-Sep-21 4:41 
GeneralRe: What about datediff? Pin
Malte Klena15-Sep-21 6:29
MemberMalte Klena15-Sep-21 6:29 
QuestionIs there a reason you don't use... Pin
Member 1486763215-Sep-21 2:39
MemberMember 1486763215-Sep-21 2:39 
AnswerRe: Is there a reason you don't use... Pin
OriginalGriff15-Sep-21 3:07
mveOriginalGriff15-Sep-21 3:07 
GeneralRe: Is there a reason you don't use... Pin
Member 1486763215-Sep-21 3:40
MemberMember 1486763215-Sep-21 3:40 
GeneralRe: Is there a reason you don't use... Pin
OriginalGriff15-Sep-21 3:54
mveOriginalGriff15-Sep-21 3:54 
GeneralRe: Is there a reason you don't use... Pin
Member 1486763215-Sep-21 4:06
MemberMember 1486763215-Sep-21 4:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.