TRY_CONVERT

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    Comments posted to this topic are about the item TRY_CONVERT

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71401

    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: 9747

    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)?

  • Toreador

    SSChampion

    Points: 11231

    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: 3101

    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: 2900

    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