SQL Server equal comparison between int and string?

Question

Looking at the return value for year() on SQL Server and it seems to be an int.

Then why does the following statement return 'equal'?

Why does the comparison operator consider int 2002 and string '2002' equal? Should it?

select 
    case 
        when year('2002-01-14 00:00:00.000') = '2002' then 'equal' 
        else 'not equal' 
    end

Yes, sql server is doing an implict cast changing '2002' to a int. it always attempts to cast the 2nd data type to the first one. — xQbert
– xQbert, Commented Nov 11, 2016 at 17:25
Whether something should or shouldn't happen doesn't matter. What matter is that you know what does happen and code accordingly. — Dan Bracuk
– Dan Bracuk, Commented Nov 11, 2016 at 17:26
Good question, and nice answer from @xQbert. There are some very good arguments against using implicit casts. As a general rule, I try to avoid them in my dbs. — David Rushton
– David Rushton, Commented Nov 11, 2016 at 17:32
@destination-data nice article supporting avoiding implicit casting. — xQbert
– xQbert, Commented Nov 11, 2016 at 17:42

xQbert · Accepted Answer · 2016-11-11 17:27:00Z

2

Because SQL server is implicitly casting '2002' to the data type of of the first value in the equality check. (int) in this case

answered Nov 11, 2016 at 17:27

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1