Yesterday... Working with Dates

Recently we ran into an issue where we were looking into why the data wasn't being returned on the first of the month for a particular web report. What we found was simple bad math, but what I learned was that sometimes efficiencies are found in what might appear more complex.

Below you will find the method used. and the problem is the subtracting of one (1) from the day of the month (DAY(GETDATE())-1). On the first of the month, this would result in a ZERO (0) which is impossible and will not return a result. The same can be said for using this sort of math on the month when your month is January. Not a good plan.

/* ---------------------------------------------
 This is the method in use...
   --------------------------------------------- 
*/
SELECT DISTINCT [DateModified]
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  YEAR(a.DateModified) = YEAR(GETDATE()) AND
  MONTH(a.DateModified) = MONTH(GETDATE()) AND
  DAY(a.DateModified) = DAY(GETDATE())-1
  ORDER BY [DateModified] Desc
GO

Another method is to use DATEDIFF() as a filter on the age (1=Yesterday). This is much heavier a load on the server and you might be better-off to avoid it.

/* ---------------------------------------------
 This method is inefficient and will impact 
 performance more with large tables
   --------------------------------------------- 
*/
SELECT DISTINCT [DateModified], DATEDIFF(dd,a.DateModified,GETDATE()) as Age
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  DATEDIFF(dd,a.DateModified,GETDATE())=1
  ORDER BY [DateModified] Desc
GO

Now, this combination of methods, using YEAR(), MONTH(), and DAY(), with the proper calculation of Yesterday is the charm. By setting the variable at the beginning, before the query, it is calculated once and the values for Year, Month, and Day are a simple comparison rather than the more complex effort that goes into DATEDIFF() from our prior example.

/* ---------------------------------------------
 This is the method adopted...
   --------------------------------------------- 
*/
DECLARE @Yesterday as DateTime;
SET @Yesterday=DATEADD(dd,-1,GETDATE());

SELECT DISTINCT [DateModified]
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  YEAR(a.DateModified) = YEAR(@Yesterday) AND
  MONTH(a.DateModified) = MONTH(@Yesterday) AND
  DAY(a.DateModified) = DAY(@Yesterday)
  ORDER BY [DateModified] Desc
GO

Another advantage of this method is that if you want to filter on last month's data rather than Yesterday's it's a few small changes.

/* ---------------------------------------------
 This is the method adopted... Last Month
   --------------------------------------------- 
*/
DECLARE @LastMonth as DateTime;
SET @LastMonth=DATEADD(mm,-1,GETDATE());

SELECT DISTINCT [DateModified]
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  YEAR(a.DateModified) = YEAR(@LastMonth) AND
  MONTH(a.DateModified) = MONTH(@LastMonth)
  ORDER BY [DateModified] Desc
GO

The best scripters/programmers learn from their own mistakes as well as those others have made.


Addendum: 
There's another way to get "yesterday" that I saw, though I still believe the preferred method might be the adopted method above, where the WHERE condition would read/include:

(a.DateModified >= DATEADD(dd,-1,GETDATE()) AND a.DateModified <= DATEADD(dd,-0,GETDATE()))

The problem I see in this methodology is that you would get the last 24-period, not "yesterday" and the wastefulness of "DATEADD(dd,-0,GETDATE())" in that it really gives you nothing more that GETDATE() anyway. The result would give you, supposing that today might be August 10th, 2010 at 11:47:01 AM, August 9th, 2010 at 11:47:01AM through August 10th, 2010 at 11:47:01 AM. In effect the results could change through the day depending upon the time you ran the query. The adopted methodology would give you an absolute definition of yesterday, or last month, eliminating the time-of-day factor, which is preferred.