SQL Server - Select years only if they fall in the current year or previous year

I only want to get dates from the current year, and the year previous. So if it's 2011, I get dates in 2011 and dates in 2010. If it's 2012, I get dates in 2012 and 2011.

SELECT 
   DISTINC开发者_如何学编程T DATEPART(yyyy, date ) as arc_year 
FROM 
   dbo.sports 
WHERE 
   team_id = '".$id."' 
ORDER BY 
   DATEPART(yyyy, date ) DESC

Thanks in advance for any help

---

You want to avoid using a function on the column itself so an index can be used.

... WHERE date >= DATEADD(year,DATEDIFF(year,0,getdate()) - 1,0) 
      and date <  DATEADD(year,DATEDIFF(year,0,getdate()) + 1,0) ...

---

A couple things. First, you should never write queries that way. Use parameterized queries. Yours has "sql injection" written all over it.

Second, the best way to do this is:

declare @teamid int = -- some value

select * from sports 
  where (team_id = @teamid)
  and date > DATEADD(year,DATEDIFF(year,0,getdate()) - 1,0) 

If the date column can contain values greater than the current year then the query would be:

declare @startdate datetime = DATEADD(year,DATEDIFF(year,0,getdate()) - 1,0) 
declare @enddate datetime = DATEADD(year,+1,@startdate)
declare @teamid int = -- some value

select * from sports 
where (team_id = @teamid)
and date BETWEEN @startdate and @EndDate