# TRY_CONVERT

• Steve Jones - SSC Editor

SSC Guru

Points: 717406

Comments posted to this topic are about the item TRY_CONVERT

• Stewart "Arturius" Campbell

SSC Guru

Points: 71607

Nice question to end the week on, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

• richardmgreen1

SSCrazy Eights

Points: 9822

I'm not sure why it doesn't work (although the value won't convert directly using cast), but if you try:-
SELECT CAST(CAST('1.23' AS FLOAT) AS int)
that works.
Is this a flaw in the TRY_CONVERT logic or is it just going for a direct conversion without trying other options (i.e. nested CASTs)?

SSChampion

Points: 11248

richardmgreen1 - Friday, June 15, 2018 2:24 AM

I'm not sure why it doesn't work (although the value won't convert directly using cast), but if you try:-
SELECT CAST(CAST('1.23' AS FLOAT) AS int)
that works.
Is this a flaw in the TRY_CONVERT logic or is it just going for a direct conversion without trying other options (i.e. nested CASTs)?

It's going for the direct conversion,. The equivalent of your nested CAST would be

SELECT TRY_CONVERT(int,TRY_CONVERT(float, '1.23'))
which returns 1

• gvoshol 73146

Hall of Fame

Points: 3143

richardmgreen1 - Friday, June 15, 2018 2:24 AM

I'm not sure why it doesn't work (although the value won't convert directly using cast), but if you try:-
SELECT CAST(CAST('1.23' AS FLOAT) AS int)
that works.
Is this a flaw in the TRY_CONVERT logic or is it just going for a direct conversion without trying other options (i.e. nested CASTs)?

Interesting tidbits:

You can convert a decimal value to an int

DECLARE @x decimal(5,2)
SET @x = 1.23
SELECT TRY_CONVERT(int,@x)
SELECT CAST(@x as int)

That works.

But you cannot convert a character representation of a decimal to an int

DECLARE @a char(5)
SET @a = '1.23'
SELECT TRY_CONVERT(int,@a)
SELECT CAST(@a as int)

That returns NULL for the first select; the second fails.

You can first cast it to decimal, and then to int

DECLARE @a char(5)
SET @a = '1.23'
SELECT TRY_CONVERT(int,TRY_CONVERT(decimal,@a))
SELECT CAST(CAST(@a as decimal) as int)

I guess that all makes sense.  If you have some character value in your db, do you really want a query to cast it to integer, if it's not an integer?

• Revenant

SSC-Forever

Points: 42467

gvoshol 73146 - Friday, June 15, 2018 5:17 AM

<big snip>
I guess that all makes sense.  If you have some character value in your db, do you really want a query to cast it to integer, if it's not an integer?

Yes. For example, you may want to store a model date of a product as a year only string, but later you may want to find how many years ago that happened. Month and day may be unknown or irrelevant, so you convert the year to int and deduct it from the current year as int.

• Japie Botma

SSCrazy

Points: 2934

Cat among the pigeons:  If you use TRY_CONVERT on a string, you as developer deliberately did so.  So then I would want a value to be returned if possible.

5ilverFox
Consulting DBA / Developer
South Africa

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply