I store dates in my database in a column of date data type.
我在日期数据类型的列中将日期存储在我的数据库中。
Let's say I have column date where I store data like this "2011-01-01", "2012-01-01", "2012-02-02" etc.
假设我有列日期，我存储的数据如“2011-01-01”，“2012-01-01”，“2012-02-02”等。
Now I need to make SQL that selects only rows where date is equal to 2012
现在我需要使SQL只选择日期等于2012的行
SELECT * FROM table WHERE date=hasSomehowYearEqualTo=2012
SELECT * FROM table WHERE date = hasSomehowYearEqualTo = 2012
What would be the query like?
查询会是什么样的？
3 个解决方案
#1
16
Do NOT use YEAR(date) - this will calculate YEAR(date) for all dates, even for those, you never use. It will also make use of an index impossible - worst case on the DB layer.
不要使用YEAR（日期） - 这将计算所有日期的YEAR（日期），即使是那些你永远不会使用的日期。它还将使用不可能的索引 - 在DB层上最坏的情况。
Use
使用
$sql="SELECT * FROM table WHERE `date` BETWEEN '$year-01-01' AND '$year-12-31'"
As a general rule: If you have the choice between a calculation on a constant and a calculation on a field, use the former.
作为一般规则：如果您可以选择常量计算和字段计算，请使用前者。
#2
7
Check out the YEAR() docs function for MySQL
查看MySQL的YEAR（）docs函数
SELECT * FROM table WHERE YEAR(date)=2012
#3
-2
select * from table where DATEPART(YEAR,[DATE])=2012
从DATEPART（年，[日期]）= 2012的表中选择*
#1
16
Do NOT use YEAR(date) - this will calculate YEAR(date) for all dates, even for those, you never use. It will also make use of an index impossible - worst case on the DB layer.
不要使用YEAR（日期） - 这将计算所有日期的YEAR（日期），即使是那些你永远不会使用的日期。它还将使用不可能的索引 - 在DB层上最坏的情况。
Use
使用
$sql="SELECT * FROM table WHERE `date` BETWEEN '$year-01-01' AND '$year-12-31'"
As a general rule: If you have the choice between a calculation on a constant and a calculation on a field, use the former.
作为一般规则：如果您可以选择常量计算和字段计算，请使用前者。
#2
7
Check out the YEAR() docs function for MySQL
查看MySQL的YEAR（）docs函数
SELECT * FROM table WHERE YEAR(date)=2012
#3
-2
select * from table where DATEPART(YEAR,[DATE])=2012
从DATEPART（年，[日期]）= 2012的表中选择*