SQL select * from column where year = 2010

This is probably a simple where clause but I want to say, from columnX (which is datetime) I want all rows where just the year = 2010.

so:

select * from mytable where Columnx =

Answer

select * from mytable where year(Columnx) = 2010

Regarding index usage (answering Simon’s comment):

if you have an index on Columnx, SQLServer WON’T use it if you use the function “year” (or any other function).

There are two possible solutions for it, one is doing the search by interval like Columnx>=’01012010′ and Columnx<=’31122010′ and another one is to create a calculated column with the year(Columnx) expression, index it, and then do the filter on this new column