'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 =
sql


Solution 1:[1]

If i understand that you want all rows in the year 2010, then:

select * 
  from mytable 
 where Columnx >= '2010-01-01 00:00:00' 
       and Columnx < '2011-01-01 00:00:00'

Solution 2:[2]

T-SQL and others; 

select * from t where year(Columnx) = 2010

Solution 3:[3]

its just simple

  select * from myTable where year(columnX) = 2010

Solution 4:[4]

NB: Should you want the year to be based on some reference date, the code below calculates the dates for the between statement:

declare @referenceTime datetime = getutcdate()
select *
from myTable
where SomeDate 
    between dateadd(year, year(@referenceTime) - 1900, '01-01-1900')                        --1st Jan this year (midnight)
    and dateadd(millisecond, -3, dateadd(year, year(@referenceTime) - 1900, '01-01-1901'))  --31st Dec end of this year (just before midnight of the new year)

Similarly, if you're using a year value, swapping year(@referenceDate) for your reference year's value will work

declare @referenceYear int = 2010
select *
from myTable
where SomeDate 
    between dateadd(year,@referenceYear - 1900, '01-01-1900')                       --1st Jan this year (midnight)
    and dateadd(millisecond, -3, dateadd(year,@referenceYear - 1900, '01-01-1901')) --31st Dec end of this year (just before midnight of the new year)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 onedaywhen
Solution 2 Alex K.
Solution 3 Barry Kaye
Solution 4 JohnLBevan

Related Questions