Select data from previous day, previous week, previous month or previous year

DATEADD method

The DATEADD method returns a specified date with the specified number interval (signed integer) added to a specified DATEPART of that date.
(http://technet.microsoft.com/en-us/library/ms186819.aspx)

With DATEADD, you can add a period of time to your date. Plus is in the future, minus is in the past. DATEADD gives you the results you want if you want to search for a specific time.

It depends on the time frame you wish to search in. If you want the data from last day or only 5 days ago, then DATEADD is fine. If you want the data for the last 5 days then you are talking about a period of time starting 5 days ago till now (or yesterday). Then we need to use DATEDIFF within DATEADD. The same applies if you search on other intervals (weeks, months, quarters, years).

You should use the DATEADD method to all parts of your date if you have for example multiple months and/or years. Otherwise you will end up with the data of all previous selected day number available.

Example:

SELECT * FROM yourTable
WHERE  DATEPART(YEAR,YourDate) = DATEPART(YEAR,DATEADD(day,-1,GETDATE()))
 AND   DATEPART(MONTH,YourDate) = DATEPART(MONTH,DATEADD(day,-1,GETDATE()))
 AND   DATEPART(DAY,YourDate) = DATEPART(DAY,DATEADD(day,-1,GETDATE()))

Using the day as interval and splitting the month and year from the date you ensure three facts:

1. Ignore time, in this case irrelevant
2. When it is the first day of the month, the query automatically goes and checks the previous month. When it is the first day of the year, it also looks in the previous year
3. Only get the wanted year and month. For example, today it is 2014-03-25. If we only use the day part of the date, the query will return all records with the day number 25. This will apply for Jan 25th, Feb 25th and Mar 25th. If you have data in 2013, you will get the 25th of all months present in your dataset for 2013.

If your interval is a month´s period, then just drop the last part of the query (DATEADD -> day) and change your interval in the DATEADD part of the query to MONTH

Example:

SELECT * FROM yourTable
WHERE  DATEPART(YEAR,YourDate) = DATEPART(YEAR,DATEADD(month,-1,GETDATE()))
 AND   DATEPART(MONTH,YourDate) = DATEPART(MONTH,DATEADD(month,-1,GETDATE()))

You can do the same with a week´s period.

SELECT * FROM yourTable
WHERE  DATEPART(YEAR,YourDate) = DATEPART(YEAR,DATEADD(week,-1,GETDATE()))
 AND   DATEPART(MONTH,YourDate) = DATEPART(MONTH,DATEADD(week,-1,GETDATE()))
AND   DATEPART(WEEK,YourDate) = DATEPART(WEEK,DATEADD(week,-1,GETDATE()))

Mind that a -1 week returns the record from last week and it depends on your settings which days will be part of this (Sunday to Saturday or Monday to Sunday etc). This is totally different that the question: give me all sales for the last 7 days. Seven days are also a week, but not calendar week.

DATEDIFF method

The DATEDIFF method returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
(http://technet.microsoft.com/en-us/library/ms189794.aspx)

By using the DATEDIFF function together with a specific DATEPART, let’s say DAY, we eliminate the existence of time in a date value. Together with the DATEADD method, we can determine the minimum date by subtracting the required days of your start off date. Then you can use your start off date as the maximum date for the comparison.  Usually the start off day is today-> GETDATE(). This way we can set a start and an end date for our query without bothering for the eventually existent time.

Get today no time:

SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

Get yesterday no time:

SELECT DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0)

Below query will return all rows from only yesterday:

SELECT * FROM yourTable
WHERE YourDate BETWEEN dateadd(day,datediff(day,1,GETDATE()),0)
AND dateadd(day,datediff(day,0,GETDATE()),0)

If you, for example, wish to retrieve all data for the last 5 days your query becomes:

SELECT * FROM yourTable
WHERE YourDate BETWEEN dateadd(day,datediff(day,5,GETDATE()),0)
AND dateadd(day,datediff(day,0,GETDATE()),0)

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL)