SQL Server: Get data for only the past year

The following adds -1 years to the current date:

SELECT ... From ... WHERE date > DATEADD(year,-1,GETDATE())

Well, I think something is missing here. User wants to get data from the last year and not from the last 365 days. There is a huge diference. In my opinion, data from the last year is every data from 2007 (if I am in 2008 now). So the right answer would be:

SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1

Then if you want to restrict this query, you can add some other filter, but always searching in the last year.

SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1 AND DATE > '05/05/2007'

Look up dateadd in BOL

dateadd(yy,-1,getdate())

The most readable, IMO:

SELECT * FROM TABLE WHERE Date >
   DATEADD(yy, -1, CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)))

Which:

1. Gets now's datetime GETDATE() = #8/27/2008 10:23am#
2. Converts to a string with format 101 CONVERT(varchar, #8/27/2008 10:23am#, 101) = '8/27/2007'
3. Converts to a datetime CONVERT(datetime, '8/27/2007') = #8/27/2008 12:00AM#
4. Subtracts 1 year DATEADD(yy, -1, #8/27/2008 12:00AM#) = #8/27/2007 12:00AM#

There's variants with DATEDIFF and DATEADD to get you midnight of today, but they tend to be rather obtuse (though slightly better on performance - not that you'd notice compared to the reads required to fetch the data).

GETDATE() returns current date and time.

If last year starts in midnight of current day last year (like in original example) you should use something like:

DECLARE @start datetime
SET @start = dbo.getdatewithouttime(DATEADD(year, -1, GETDATE())) -- cut time (hours, minutes, ect.) --  getdatewithouttime() function doesn't exist in MS SQL -- you have to write one
SELECT column1, column2, ..., columnN FROM table WHERE date >= @start

For some reason none of the results above worked for me.

This selects the last 365 days.

 SELECT ... From ... WHERE date BETWEEN CURDATE() - INTERVAL 1 YEAR AND CURDATE()

The other suggestions are good if you have "SQL only".

However I suggest, that - if possible - you calculate the date in your program and insert it as string in the SQL query.

At least for for big tables (i.e. several million rows, maybe combined with joins) that will give you a considerable speed improvement as the optimizer can work with that much better.

I had a similar problem but the previous coder only provided the date in mm-yyyy format. My solution is simple but might prove helpful to some (I also wanted to be sure beginning and ending spaces were eliminated):

SELECT ... FROM ....WHERE 
CONVERT(datetime,REPLACE(LEFT(LTRIM([MoYr]),2),'-
','')+'/01/'+RIGHT(RTRIM([MoYr]),4)) >=  DATEADD(year,-1,GETDATE())

Here's my version.

YEAR(NOW())- 1

Example:

YEAR(c.contractDate) =  YEAR(NOW())- 1

For me this worked well

SELECT DATE_ADD(Now(),INTERVAL -2 YEAR);