TRY_CONVERT returns wrong result

%3CLINGO-SUB%20id%3D%22lingo-sub-1670670%22%20slang%3D%22en-US%22%3ETRY_CONVERT%20returns%20wrong%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1670670%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20relying%20on%20correct%20result%20of%20Try_Convert%20(%20select%20try_convert(smallint%2C'%2B'))%2C%20but%20instead%20of%20null%2C%20I%3CBR%20%2F%3Eve%20received%200%20as%20a%20result.%20It%20is%20the%20same%20also%20with%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAccording%20to%20the%20documentation%20(%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fde-de%2Fsql%2Ft-sql%2Ffunctions%2Ftry-convert-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fde-de%2Fsql%2Ft-sql%2Ffunctions%2Ftry-convert-transact-sql%3Fview%3Dsql-server-ver15%3C%2FA%3E)%20this%20should%20be%20not%20possible.%20Bug%20or%20am%20I%20missing%20something%20%3F%3C%2FP%3E%3CP%3EI've%26nbsp%3Btried%20it%20on%20SQL%202012%20(latest%20SP4)%20and%20SQL%202019%20(latest%20CU)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EBobi%3C%2FP%3E%3C%2FLINGO-BODY%3E
Senior Member

Hi

 

I was relying on correct result of Try_Convert ( select try_convert(smallint,'+')), but instead of null, I've received 0 as a result. It is the same also with -

 

According to the documentation ( https://docs.microsoft.com/de-de/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15) this should be not possible. Bug or am I missing something ?

I've tried it on SQL 2012 (latest SP4) and SQL 2019 (latest CU)

 

Thanks,

Bobi

1 Reply

The result 0 is correct.

The plus and minus counts as numeric signs, an addition number is not mandatory here

Convert returns the same result

 

select convert(smallint, '+'), convert(smallint, '-')