Conversion failed when converting the varchar value 'Oct ' to data type int

2
votes
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.

Datatype for Date_of_contract column is datetime

1
sqlsql-server

1 Answers

3
votes

Don't cast the datetime to a string and try and extract the year.

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.