DATALENGTH

  • Comments posted to this topic are about the item DATALENGTH

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

  • 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

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

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

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

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

  • Interesting that this does not work the same way.

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

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

  • 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

  • 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

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

  • Nice question, thanks!

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

  • 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

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

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

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