SELECT StartDate = DATEADD(wk,-11,DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0)) --Monday, 11 Weeks ago
,EndDate = DATEADD(wk, 1,DATEADD(dd,DATEDIFF(dd,6,GETDATE())/7*7,6)) --This coming Sunday
The "0" and the "6" are known as DATE SERIAL NUMBERS and represent the number of days since the SQL Server Base Date of 01 Jan 1900.
"0" is the first of January 1900, which is a Monday.
"6" is the first Sunday in 1900.
"/7*7" is an integer math method of rounding down to the day evenly divisible by 7... in other words, weeks that start with the reference day.
These types of temporal calculations are fundamental and essential to high performance reporting. I recommend doing a deep dive on how they work and some of the incredible things you can do with such knowledge of temporal manipulation.
Calendar tables have their uses but, if someone suggests a Calendar table for this, I recommend you ignore them because it will be slower and more read intensive than the formula above and certainly more complex to maintain.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)