SQL DATEDIFF Magic: Yesterday, This Week, This Month and More

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-1GETDATE() ) 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( dCreateDateGETDATE() ) = 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( dCreateDateGETDATE() ) < 7

Last 30 Days:

 WHERE DATEDIFF( dCreateDateGETDATE() ) < 30

Or, to be more exact we can say this month, this week, this year:

WHERE DATEDIFF( mCreateDateGETDATE() ) = 0 WHERE DATEDIFF( wwCreateDateGETDATE() ) = 0 WHERE DATEDIFF( yyCreateDateGETDATE() ) = 0

Let’s add one more yet: last month, last week, last year:

WHERE DATEDIFF( mCreateDateGETDATE() ) = 1 WHERE DATEDIFF( wwCreateDateGETDATE() ) = 1

WHERE DATEDIFF( yyCreateDateGETDATE() ) = 1

10 comments » Write a comment

  1. this may look good, but it will not scale. if you want your WHERE clause to use an index, then your indexed column must be on the left side of the equal sign by itself. if you wrap a column in a function, it can not use an index.

  2. If you have an index on CreateDate then this method:

    WHERE DATEDIFF( d, CreateDate, GETDATE() ) = 1

    will be vastly less efficient than

    WHERE CreateDate >= DATEADD( d, -1, GETDATE() ) AND CreateDate < GETDATE()

    The first method will have to scan the entire table or index, the second can do a seek. So the DATEDIFF should definitely be avoided. Epic fail.

  3. Matt: no. dateadd and datediff don’t do math the same way. datediff splits on calendar intervals.

    example: select datediff(yy,’2001-12-31′,’2000-01-01′) returns 1 year difference even though they’re only 1 day apart.

    dateadd adds an absolute amount. if you run your query at 4pm, it would give you everything from 4pm yesterday to 4pm today. the original example was a way to get everything from yesterday.

  4. Thank you for making this mistake in public as you just totally saved my bacon! Idiots of the world unite. I’m surprised SQL server doesn’t know how to optimise simple datediff expressions into something that can use an index though.

  5. Can any one please suggest how to retrieve data between start date previous week and last date of previous week

  6. Try
    WHERE DateColumn >= DATEADD(wk, -2, GetDate()) AND DateColumn < = DATEADD(wk, -1, GetDate())

  7. Please note that the above function for yesterday is incorrect, as it returns records which are within 24 hours from now. This means, if you you run the query today at 03:00 pm then it will return not all the records of yesterday; instead it will return only those records from yesterday that are on or after 03:00 pm. Also, it will return today’s records as well. Same issue is with last month, last year, etc.

    The right query should return records between yesterday 12:00 am (inclusive) and today 12:00 am (exclusive).

    I just wanted to clarify this to anyone using these queries so that they may not use incorrect ones.

Leave a Reply

Required fields are marked *.