SELECT *
FROM MyTable
WHERE SUBSTRING(CAST(Date_of_contract AS varchar(38)), 1, 4) = 2017
But all I get is this error:
但我得到的是这个错误:
Conversion failed when converting the varchar value 'Oct ' to data type int.
当将varchar值'Oct '转换为数据类型int时，转换失败。
Datatype for Date_of_contract column is datetime
Date_of_contract列的数据类型是datetime
1 个解决方案
#1
3
Don't cast the datetime to a string and try and extract the year.
不要将datetime强制转换为字符串，并尝试提取年份。
Just use
只使用
SELECT *
FROM MyTable
where YEAR(Date_of_contract) = 2017
Or
或
SELECT *
FROM MyTable
where Date_of_contract >= '20170101' AND Date_of_contract < '20180101'
The second one is preferable if there is an index on the column as the first one is not sargable.
如果列上有索引，则第二个索引更可取，因为第一个索引不可sargable。
#1
3
Don't cast the datetime to a string and try and extract the year.
不要将datetime强制转换为字符串，并尝试提取年份。
Just use
只使用
SELECT *
FROM MyTable
where YEAR(Date_of_contract) = 2017
Or
或
SELECT *
FROM MyTable
where Date_of_contract >= '20170101' AND Date_of_contract < '20180101'
The second one is preferable if there is an index on the column as the first one is not sargable.
如果列上有索引，则第二个索引更可取，因为第一个索引不可sargable。