Getting all Yesterday’s Sales, or Finding Midnight Yesterday

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I see questions like this regularly. How do I get all the sales from yesterday? I tried using DATEADD(day, –1, getdate()), but I only get some of the sales.

Many people working with T-SQL know this is an issue. They know that getdate() returns the date and time of this instant (roughly). At the time of this writing, that’s 3:11 pm.

2015-11-25 15_11_29-Photos

However if I want sales from yesterday, I really want all timestamps from midnight on. So I probably want code that looks like this:

SELECT SUM(ordertotal)
 FROM sales
 WHERE SalesDate > '20151124 00:00:00'
 AND SalesDate < '20151125 00:00:00'

How do I get the time to be midnight?

The easy answer is one I’ve been using quite a bit lately to answer questions, and I’ve refreshed my knowledge of the datetime trick. I use a combination of DATEADD and DATEDIFF to get to a 0 based datetime.

SELECT DateAdd(Day, Datediff(Day,0, GetDate()), 0)

In this case, I’ll get midnight yesterday, or 2015-11-24 00:00:00. This is because I’m using 0 as my base date and looking for the days (in DATEDIFF) since that 0 based date. When I add those days with DATEADD to the same zero based date, I get the correct date, but with a 0 based time.

This same technique works to find the first of this month.

SELECT DateAdd(Month, Datediff(Month,0, GetDate()), 0)

You can also use other datetime values to normalize those times.

SQLNewBlogger

This was a quick post. I had answered the question and spent less than ten minutes putting this together.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

2 Responses to Getting all Yesterday’s Sales, or Finding Midnight Yesterday

  1. On 2008+ it is quicker to convert to DATE. And be careful about using the DATEDIFF trick, you can get funky estimates when the calculation is in a predicate (the optimizer swaps the date inputs). http://sqlperformance.com/2013/09/t-sql-queries/datediff-bug

Comments are closed.