TRY_CONVERT smaller

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    Comments posted to this topic are about the item TRY_CONVERT smaller

  • This was removed by the editor as SPAM

  • HappyGeek

    SSCoach

    Points: 18666

    Good question, thanks

    ...

  • Neil Burton

    SSC-Insane

    Points: 21945

    To be pedantic, it depends on what edition you run the code. I know that the unwritten rule is assume the latest version but many people testing that, like I did, will get the wrong answer.

    Still, I've got my point back by complaining about it ๐Ÿ˜Ž


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    โ€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Carlo Romagnano

    SSC-Insane

    Points: 21793

    That's easy!

    But, try to answer this:

    "What's the resut?"

    SELECT TRY_CONVERT(VARCHAR(1), {D'2011-MAY-01'})

    :-D:-D:-D

  • edwardwill

    SSCarpal Tunnel

    Points: 4907

    Msg 195, Level 15, State 10, Line 1

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

  • Martin Alvey

    Ten Centuries

    Points: 1036

    Msg 195, Level 15, State 10, Line 1

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

    for me here...and then I realised it was on a 2008 R2 instance...

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    I *almost* gave the answer that would have been correct for SELECT TRY_CONVERT(VARCHAR(1), 234523). Almost.

    Also, I would like to know how many people would have selected NULL if that had been an option.


    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

    Carlo Romagnano (2/18/2016)


    That's easy!

    But, try to answer this:

    "What's the resut?"

    SELECT TRY_CONVERT(VARCHAR(1), {D'2011-MAY-01'})

    :-D:-D:-D

    The correct answer to this question is "it depends".

    Now my question to you is if you can figure out what it depends on.

    (And no, I am not referring to the version of SQL Server)


    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/

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question. Thanks for sharing Steve.

  • Carlo Romagnano

    SSC-Insane

    Points: 21793

    Hugo Kornelis (2/18/2016)


    Carlo Romagnano (2/18/2016)


    That's easy!

    But, try to answer this:

    "What's the resut?"

    SELECT TRY_CONVERT(VARCHAR(1), {D'2011-MAY-01'})

    :-D:-D:-D

    The correct answer to this question is "it depends".

    Now my question to you is if you can figure out what it depends on.

    (And no, I am not referring to the version of SQL Server)

    Only one answer:

    Msg 241, Level 16, State 3, Line 1

    Conversion failed when converting date and/or time from character string.

    Because {D'2011-MAY-01'} is not a valid date! It doesn't dipend on language settings. The right format for ODBC datetime type is {D'2011-05-01'} (yyyy-mm-dd)

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Ouch. Burned.

    Thanks, Carlo! I really should have known that.


    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/

  • g.maxfield

    Default port

    Points: 1435

    I got error message too so I failed the question.

    Msg 195, Level 15, State 10, Line 3

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

  • djj

    SSCoach

    Points: 18738

    You should have included NULL. That way I could have gotten it wrong with NULL instead of error. ๐Ÿ™‚

    Shows you what reading the help for a function gives you. My gut said 2 but after reading the Microsoft information I thought it would be error.

    Missed the part about truncation in TRY_CONVERT.

  • g.maxfield

    Default port

    Points: 1435

    I was using SQL Server 2014 instance

    Microsoft SQL Server Management Studio 12.0.2000.8

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

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