SQL select * from column where year = 2010
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9891025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL select * from column where year = 2010
提问by Diego
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.
这可能是一个简单的 where 子句，但我想说，从 columnX（日期时间）开始，我想要年份 = 2010 的所有行。
so:
所以：
select * from mytable where Columnx =
回答by Diego
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).
如果您在 Columnx 上有索引，并且您使用函数“year”（或任何其他函数），SQLServer 将不会使用它。
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
有两种可能的解决方案，一种是按间隔进行搜索，如 Columnx>='01012010' 和 Columnx<='31122010'，另一种是使用 year(Columnx) 表达式创建一个计算列，对其进行索引，然后然后在这个新列上做过滤
回答by Simon
If i understand that you want all rows in the year 2010, then:
如果我知道您想要 2010 年的所有行，那么：
select *
from mytable
where Columnx >= '2010-01-01 00:00:00'
and Columnx < '2011-01-01 00:00:00'
回答by Alex K.
T-SQL and others;
T-SQL 等；
select * from t where year(Columnx) = 2010
回答by Kishore Kumar
its just simple
它很简单
select * from myTable where year(columnX) = 2010
回答by JohnLBevan
NB: Should you want the year to be based on some reference date, the code below calculates the dates for the betweenstatement:
注意：如果您希望年份基于某个参考日期，下面的代码将计算between语句的日期：
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
同样，如果您使用的是年份值，则交换year(@referenceDate)参考年份的值将起作用
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)
