TRY_CAST('<VALID NUMBER>' AS INT) returns NULL, would expect the number itself

Question

Hi,

When converting a (N)VARCHAR number with TRY_CAST to INT the result is NULL.

TRY_PARSE returns the right result.

Example:

SELECT	TryCastNumber	= TRY_CAST	(14.0 AS INT)
	,	TryCastVARCHAR	= TRY_CAST	('14.0' AS INT)
	,	TryParseVARCHAR = TRY_PARSE	('14.0' AS INT)

Returns:

TryCastnumber = 14, -- As expected

TryCastVARCHAR = NULL, -- Expected 14!!!

TryParseVARCHAR = 14 -- As expected

Is this correct behaviour?

Many thanks

Lou Driehuis

Answer 1

Hi @Lou Driehuis

I did a simple test.

select CastVARCHAR	= CAST	('14.0' AS INT)

An error is reported.

If you want to get 14, you can try replacing int with DECIMAL(2,0).

 SELECT	TryCastNumber	= TRY_CAST	(14.0 AS INT)
	,	TryCastVARCHAR	= TRY_CAST	('14.0' AS DECIMAL(2,0))
	,	TryParseVARCHAR = TRY_PARSE	('14.0' AS INT)

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

SELECT CAST('14.0' as int)

... raises an error, so it is an expected behaviour of TRY_CAST