
‘Just copy and paste this query, go to the WHERE clause, change it to last week, and run it. It’s simple!’ – Some people you might know
Manually changing values in a query on a daily/weekly/monthly cadence can be a huge pain. It also opens the door for human error. We’ll review the power of Sql‘s GETDATE( ) function, how to include it in queries (maybe you have a report that is always last week’s data, for example), and, very importantly, how to avoid a few issues with date/week ranges.
What Does This Thing Even Do?
As with all tools, understanding what a function does is very helpful. Today’s date is 2021–08–27. Let’s take a look at what running a GETDATE will give us (formatted as ‘yyyy-MM-dd’):

It GETs the DATE on which it’s executed. Not quite the ground-shaking shock you may have felt when you found out Bruce Willis was really dead the whole time during ‘The Sixth Sense’…but really close.
Automate Last Week’s Report
So, let’s say you have data that needs to be pulled each Monday. The report is to include data from only the previous week. Do not include weeks prior to last week or the partial current week.
For this, you will need some sort of Calendar table. In it, you will ideally have a column that is a year-week combo. Please include some sort of delimiter between the year and week. If your company doesn’t already have this, I am assuming you are reading this on your own computer/cell phone because your company might not even have computers yet.
Now, obviously looking at today’s date will not give us information on what we call last week. Let’s take a look at a table with ‘calendar_date’ and ‘year_week’ columns for this week and last.

GETDATE() – 7?
You might be accustomed to using something like a DATEADD function where one of the arguments is a number and another argument is the interval type (year, day, etc.). When using GETDATE, you can just simple say ‘-7’ to get the date a week ago. Let’s now look at what we’ll get when we select GETDATE( ) and GETDATE( ) – 7 from our ‘calendar_table’.

Let’s say we are running this on a Monday. In this example, we could select 2021–08–16 as last week’s Monday and pull all data WHERE ‘year_week’ is equal to the value in our ‘calendar_table’, again ‘2021–33’ for this example. If we run this on a Sunday, last week’s Sunday’s ‘year_week’ will be our variable to use, which is still ‘2021–33’. Saturday works as well. You get the picture. Things are looking up.
‘We’re making money, cha-ching, cha-ching, cha-ching. We’re making money, cha-ching, cha-ching, cha-ching. We’re making money, cha-ching, cha-ching, cha-ching. Cha, cha, cha, cha, cha, cha, ching!’ – Pamela Pupkin
Pulling It All Together
There are multiple ways to get this done. We can use a subquery to select the ‘year_week’ and have this subquery in our WHERE clause. We can Inner Join the dates needed. We can even go as far as creating a variable to house this value.
I recommend the variable route, for this example, because when you start to get into more temp tables, CTE’s, or subqueries to bring in multiple tables, naming a variable and including it in the WHERE clause for each of these is a time saver. It also reduces the amount of lines of code in the long run.
We now have our ‘daily_sales’ table and we want to pull only rows for last week. Here are the results followed by the code and finally a useful nugget of using a ‘year_flag’ column below that.

Note that if we would like include a rolling 6 week period, for example, we could create two variables. The other variable could be set to a different number of days before the GETDATE. We can then pull rows BETWEEN the two ‘year_week’ values.
Why Do Leading Zeros Matter?
When creating this ‘year_week’ column, using a leading 0 for the single digit weeks is very much needed. Essentially, these values will be read like letters, 0 =A, 1 = B, and so on. Thinking about this alphabetically, we can see what pulling and sorting results would look like if we didn’t have a leading 0. In this example, we pulled values BETWEEN ‘2021–1’ and ‘2021–3’.
Note that weeks 10, 105 (just for show), 11, and 12 are included. In our ‘calendar_table’ we only have weeks 1–12 (plus 105). Weeks 13+ were not included. If they had been, all weeks between 10 and 29 would have shown up as well. Week 30 would not have shown up because that’s ‘DA’, which comes after ‘D’.

In this example, we can see that if we ORDER BY ‘year_week’ or by ‘year_week_alpha’, we get the same results.
The Sky’s the Limit!
We’re not limited to just pulling ranges by day or week. We can be creative and use quarters or years. We can set up things like always year-to-date.
We can even use some rules that say ‘always include at least 10 weeks in the output (go back to previous year if needed), but after week 10 of a new year, then include all weeks for current year and drop last year’. Something like this is helpful when a new year starts and people don’t want to see visualizations in a Power BI report with a single week’s worth of data.
For a little more information on Joins and Unions, check out this more detailed article. As always, fun visualizations and super dad jokes are included.
Final Thoughts
SQL is a powerful tool that makes life much easier. It can be a huge time saver when used correctly. When using routine queries, the GETDATE( ) function is a blessing wrapped in a warm fuzzy glitter cloud of puppies.
That’s our lesson for today. As always, keep on learnin’!