TRY_CONVERT

  • Comments posted to this topic are about the item TRY_CONVERT

  • 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”

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

  • 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

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

  • 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.

  • 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 6 (of 6 total)

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