DATALENGTH

  • ssismaddy

    SSCarpal Tunnel

    Points: 4850

    Comments posted to this topic are about the item DATALENGTH

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice to be reminded of the basics - hence a good question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    It is a good question about the basics, but not a very original one. I believe this sort of question has already been asked multiple times.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • ziangij

    SSCertifiable

    Points: 7207

    good one... thanks...:-)

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    I would have had to give it a little more consideration if the options had included 1,3 and 3,1.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    da-zero (8/23/2010)


    It is a good question about the basics, but not a very original one. I believe this sort of question has already been asked multiple times.

    I'd say at least 10 times, and then some for a couple more gotchas. I don't mind anual review but monthly and more seems like a bit too much ofr my taste.

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    This question proves that what I am learning on QoTD is actually sticking. Thanks.

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    Interesting that this does not work the same way.

    select datalength(convert(varchar, getdate(), 101))

  • JF1081

    SSC Eights!

    Points: 982

    At first I thought 3,1, but since that was not an option I came to my senses and realized that varchar does not adjust if the string is too big.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    kevin.l.williams (8/23/2010)


    Interesting that this does not work the same way.

    select datalength(convert(varchar, getdate(), 101))

    See http://msdn.microsoft.com/en-us/library/aa258242(v=SQL.80).aspx.

    When n is not specified with the CAST function, the default length is 30.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Oleg Netchaev

    SSCertifiable

    Points: 5272

    kevin.l.williams (8/23/2010)


    Interesting that this does not work the same way.

    select datalength(convert(varchar, getdate(), 101))

    This is by design and has also been covered many times in recent QotDs. Forgetting to specify the size when declaring a local variable results in size defaulting to 1 while forgetting to specify the size when using convert - 30. Because the 101 format means mm/dd/yyyy, totalling 10 characters, the result of the query is 10 because the default 30 is greater than that.

    Oleg

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    Oleg Netchaev (8/23/2010)


    This is by design and has also been covered many times in recent QotDs. Forgetting to specify the size when declaring a local variable results in size defaulting to 1 while forgetting to specify the size when using convert - 30. Because the 101 format means mm/dd/yyyy, totalling 10 characters, the result of the query is 10 because the default 30 is greater than that.

    Oleg

    Thanks.

    I never like using implicit coding like this. It seems lazy and risky.

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question, thanks!

    I'm surprised after all the recent questions that were related that we still have ~25% answering this one incorrectly.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Michael Riemer

    SSCertifiable

    Points: 5136

    Nice question on the basics and all the ensuing explanations - thanks

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

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