SQL Server: Get data for only the past year

Question

I am writing a query in which I have to get the data for only the last year

What is the best way to do this

SELECT ... From ... WHERE date > '8/27/2007 12:00:00 AM' ?????

Answer 1

The following adds -1 years to the current date:

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

Answer 2

Look up dateadd in BOL

dateadd(yy,-1,getdate())

Answer 3

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

Answer 4

To get rid of time check out Get Datetime Without Time

Answer 5

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'

Answer 6

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).

Answer 7

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.