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 =
This question is related to
sql
its just simple
select * from myTable where year(columnX) = 2010
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)
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'
T-SQL and others;
select * from t where year(Columnx) = 2010
Source: Stackoverflow.com