SQL select from column where year 2010

Question

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

User · Answer

If i understand that you want all rows in the year 2010  then   select      from mytable   where Columnx  gt    2010-01-01 00 00 00          and Columnx  lt   2011-01-01 00 00 00

User · Answer

T-SQL and others    select   from t where year Columnx    2010

User · 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 lt   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

User · Answer

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

User · Answer

its just simple    select   from myTable where year columnX    2010

[sql] SQL select * from column where year = 2010

Examples related to sql