SQL Server: based on the year, find everything (inclusive) from the year of the previous and current year

Question

I am not familiar with SQL Server syntax, but I assume it will look something like this:

$year = 2010;

$query = mssql_query("SELECT * FROM dbo.events 
                      WHERE date BETWEEN $year AND $year-1");

I just don't know how to format $ year in this case.

Answer 1

You're close Assuming the column dateis a datetime data type:

SELECT *
    FROM dbo.events
    WHERE DATEPART(year, date) BETWEEN $year-1 AND $year

Answer 2

SQL Server has a YEAR function that is even shorter than DATEPART:

select * from dbo.events where year(date) between $year-1 and $year

Answer 3

Are you trying to choose only one year?

This works for me:

$year = 2011;
select * from table where year(date_field) = $year

Answer 4

Try the following:

declare @year int
set @year=2010

select * from FROM dbo.events 
WHERE cast(YEAR([date]) as int) <= @year 
and cast(YEAR([date]) as int) >= @year-1