我有一个表,其中包含以下字段:
State_Code School_Code Established_Year Data_Year
12 12016837383 1962 2011-12
12 12018383799 1996 2011-12
12 12024895748 2007 2011-12
12 12037849837 1987 2011-12
在这里,所有这些字段均为VARCHAR类型。
我想检索所有在1990年之前建立的学校。我触发了此查询:
Select
School_Code,
date_format(str_to_date(Established_Year,'%y'),'%y') as Year,
date_format(str_to_date(Data_Year,'%y'),'%y') as Data_Year
from
Table1
WHERE
Established_Year < 1990;
但它返回以下内容:
School_Code Year Data_Year
12016837383 19 20
12037849837 19 20
我也尝试使用
CONVERT
函数,如下所示:Select
School_Code,
Convert(Established_Year, YEAR) as Year
from
Table1
WHERE
Established_Year < 1990;
但这会引发此错误:
Error Code: 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax
to use near 'YEAR' at line 1
最佳答案
您实际上可以将其转换为int。
SELECT *
FROM TableName
WHERE CAST(Established_Year AS UNSIGNED) < 1990
更新1
根据您的评论,如果要检索其Data_Year在2010-11之前的School_Code,则需要将
Data_Year
转换为实际日期。SELECT School_Code
FROM TableName
WHERE STR_TO_DATE(CONCAT(Data_Year, '-01'), '%Y-%m-%d') < DATE('2010-11-01')
关于mysql - 将字符串值选择为Year格式,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15985272/