将字符串值选择为Year格式

将字符串值选择为Year格式

我有一个表,其中包含以下字段:

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/

10-11 02:45