I have never claimed to be a SQL magician. I say that I have functional SQL skills. If I need to make something happen, I can make it happen. It may not always be the best solution up front, but it works. From there, I improve on it as I go. Which, really, is why I love what I do. There is always a better way to do it. Up until today, to select results based on a date or date range I used something along the lines of
WHERE CreateDate >= DATEADD( d, -1, GETDATE() ) AND CreateDate < GETDATE()
Which, again, functions but as I learned today, may not be the best way to get a result set from yesterday or the last x amount of days. Using DATEDIFF we can make some magic happen:
EDIT: The much more brilliant folks than me over at reddit have pointed out that I am an idiot and this is far less efficient. So, even though its fun and different, I am clearly in the wrong.
Limit results to yesterday:
WHERE DATEDIFF( d, CreateDate, GETDATE() ) = 1
Esentially this is saying that if the CreateDate is one day before the current date (GETDATE()) than give me that result set.
Using this same logic we can say last 7 days:
WHERE DATEDIFF( d, CreateDate, GETDATE() ) < 7
Last 30 Days:
WHERE DATEDIFF( d, CreateDate, GETDATE() ) < 30
Or, to be more exact we can say this month, this week, this year:
WHERE DATEDIFF( m, CreateDate, GETDATE() ) = 0
WHERE DATEDIFF( ww, CreateDate, GETDATE() ) = 0
WHERE DATEDIFF( yy, CreateDate, GETDATE() ) = 0
Let's add one more yet: last month, last week, last year:
WHERE DATEDIFF( m, CreateDate, GETDATE() ) = 1
WHERE DATEDIFF( ww, CreateDate, GETDATE() ) = 1
WHERE DATEDIFF( yy, CreateDate, GETDATE() ) = 1
