Recently at work, a manager asked that I show some records but restrict them based on the calendar Sunday through Saturday week of a given date.
I did my usual Google search for this particular request, which gave me some ideas though none really gave exactly what I wanted because for some reason, Sundays throw certain expected calculations off.
With that in mind, I decided to write some extra lines just to make it more clear to me what I was accomplishing to get the dates and figured it might be useful to other visitors to this blog.
So, here it is, this is written specifically for SQL Server 2008.
Declare @DateToCheck datetime
Declare @SubtractForSunday int
Declare @AddForSaturday int
Declare @SundayDate datetime
Declare @SaturdayDate datetime
Set @DateToCheck = DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))
Set @SubtractForSunday = DATEPART(dw,@DateToCheck) - 1
Set @AddForSaturday = 7 - DATEPART(dw,@DateToCheck)
Set @SundayDate = DateAdd(day,-@SubtractForSunday,@DateToCheck)
Set @SaturdayDate = DateAdd(day,@AddForSaturday,@DateToCheck)
Where 1 = 1
and dtSomeDate between @SundayDate and @SaturdayDate
To break it down a little so you know what I am doing:
The DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())) portion is to set the current date/time to midnight. You can get around this by making the @DateToCheck variable a date variable, instead of datetime, or making sure the datetime variables you are working with already set to midnight, which was the case for my own particular situation. I’m showing the midnight here because that is not always the case and is useful for my own reference.
Sunday is always day 1 and Saturday is always day 7. Those are known quantities. Once we have a date to check, we can get that date’s day of the week number that is 1 through 7 using algebra. If the day we are checking is 1 (Sunday) and we are aiming to get to 1 (Sunday) , we will want to subtract nothing, 1 – 0 = 1. If the day is 2 (Monday), we will want to subtract only 1, so 2 – 1 = 1. If the day is Saturday we will want that number to be 6, 7 – 6 = 1. We are starting to see a pattern where y is the day of the week are checking and x is the number that will give us the necessary subtraction for reaching 1: y – x = 1. We take that same logic to give us Saturday: v + w = 7. The @SubtractForSunday variable is determined by re-working this equation to look like x = y – 1 and the @AddForSaturday variable is determined by re-working it to w = 7 – v.
Because once we have that number, we can proceed to the original form of the equation and use the DateAdd function, making sure to have our minus sign in front of our @SubtractForSunday variable.
If you wanted to condense it instead of lay out all like that, you could do:
Set @SundayDate = DateAdd(day,-(DATEPART(dw,@DateToCheck) - 1),@DateToCheck)
Set @SaturdayDate = DateAdd(day,(7 - DATEPART(dw,@DateToCheck)),@DateToCheck)
If you appreciate any of the work that went into making this post, please consider giving a tip to my PayPal account: