DATALENGTH

Question

Post reply

DATALENGTH

ssismaddy

SSCarpal Tunnel

Points: 4850
More actions
August 21, 2010 at 1:34 pm

#239305

Comments posted to this topic are about the item DATALENGTH

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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: 259105 More actions · Answer 2

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 SSCrazy Eights Points: 8066 More actions · Answer 3

ziangij

SSCrazy Eights

Points: 8066

August 23, 2010 at 4:09 am

#1210326

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

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 7

Interesting that this does not work the same way.

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

JF1081 SSC Eights! Points: 982 More actions · Answer 8

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: 259105 More actions · Answer 9

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: 5276 More actions · Answer 10

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 SSCarpal Tunnel Points: 4962 More actions · Answer 11

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 More actions · Answer 12

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: 281334 More actions · Answer 13

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: 5190 More actions · Answer 14

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