TRY_CONVERT large numbers

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715841

    Comments posted to this topic are about the item TRY_CONVERT large numbers

  • This was removed by the editor as SPAM

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question Steve, a very good reminder i almost got it wrong but the inner voice came at the right time 😉

  • Carlo Romagnano

    SSC-Insane

    Points: 21791

    The strange thing is that this code returns NULL using NVARCHAR(1)

    SELECT TRY_CONVERT(NVARCHAR(1), 234523)

  • jiken

    Old Hand

    Points: 314

    When execute below code then error message come, not * (star).

    SELECT TRY_CONVERT(VARCHAR(1), 234523)

    An error

    Msg 195, Level 15, State 10, Line 1

    'VARCHAR' is not a recognized built-in function name.

    When execute below code then answer come as *.

    SELECT CONVERT(VARCHAR(1), 234523)

    Result

    *

    So, you cut wrong point.

  • Ed Wagner

    SSC Guru

    Points: 286958

  • Carlo Romagnano

    SSC-Insane

    Points: 21791

    When execute below code then error message come, not * (star).

    SELECT TRY_CONVERT(VARCHAR(1), 234523)

    An error

    Msg 195, Level 15, State 10, Line 1

    'VARCHAR' is not a recognized built-in function name.

    When execute below code then answer come as *.

    SELECT CONVERT(VARCHAR(1), 234523)

    Result

    *

    So, you cut wrong point.

    What's the version of sqlserver?

  • jiken

    Old Hand

    Points: 314

    SQL Server 2012

  • patrick.mccausland

    Old Hand

    Points: 311

    Got the same result, an error

    Tried SELECT TRY_CINVERT(FLOAT, 234523) and got

    Msg 195, Level 15, State 10, Line 2

    'TRY_CONVERT' is not a recognized built-in function name.

  • jiken

    Old Hand

    Points: 314

    Carlo Romagnano (3/2/2016)


    When execute below code then error message come, not * (star).

    SELECT TRY_CONVERT(VARCHAR(1), 234523)

    An error

    Msg 195, Level 15, State 10, Line 1

    'VARCHAR' is not a recognized built-in function name.

    When execute below code then answer come as *.

    SELECT CONVERT(VARCHAR(1), 234523)

    Result

    *

    So, you cut wrong point.

    What's the version of sqlserver?

    SQL Server 2012

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    jiken (3/2/2016)


    When execute below code then error message come, not * (star).

    SELECT TRY_CONVERT(VARCHAR(1), 234523)

    An error

    Msg 195, Level 15, State 10, Line 1

    'VARCHAR' is not a recognized built-in function name.

    When execute below code then answer come as *.

    SELECT CONVERT(VARCHAR(1), 234523)

    Result

    *

    So, you cut wrong point.

    Try running it in a database with the compatibility level set to 110 (SQL Server 2012) or higher.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    patrick.mccausland (3/2/2016)


    Got the same result, an error

    Tried SELECT TRY_CINVERT(FLOAT, 234523) and got

    Msg 195, Level 15, State 10, Line 2

    'TRY_CONVERT' is not a recognized built-in function name.

    Try running it on SQL Server 2012 or higher.

    (As indicated in the question)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • patrick.mccausland

    Old Hand

    Points: 311

    Mea culpa,

    I AM on SQL 12 server, however, I was in a database running at compatibility level 9.

    Changed to 12 and got the expected result.

  • Ed Wagner

    SSC Guru

    Points: 286958

    Nice catch, Hugo. The first one with VARCHAR being the problem is one I still don't get, but the TRY_CONVERT bring the problem makes sense.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Ed Wagner (3/2/2016)


    Nice catch, Hugo. The first one with VARCHAR being the problem is one I still don't get, but the TRY_CONVERT bring the problem makes sense.

    TRY_CONVERT was introduced as a reserved keyword in compat level 110. Older compat levels do not recognise it as a keyword, so they assume it's a user-defined object.

    The result is that SELECT TRY_CONVERT(varchar(1), 1234) is evaluated just the same way as for instance SELECT asfsd(rtrt(), 123) - and apparently the evaluation starts at the innermost level because this raises an error about rtrt not being recognised; replace this with just a number or something and then you'll get an error about asfsd.

    (In compat 110 and up, SQL Server knows that TRY_CONVERT is special in that the first parameter is a data type name; for "normal" functions varchar(1) is never a valid argument)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 31 total)

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