SQL Server: Given a year find everything (inclusiv

问题:

I'm not familiar with SQL Server syntax, but I imagine it would go something like this:

$year = 2010;

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

I'm just not sure how to format $year in this case.

回答1:

You're close. Assuming the date column is a datetime datatype:

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

回答2:

SQL Server has the YEAR function, which is even shorter to write than DATEPART:

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

回答3:

are you trying to select only one year?

This works for me:

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

回答4:

Try this:

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