SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATALENGTH


DATALENGTH

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 1814
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
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3423 Visits: 1323
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
UMG Developer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 2204
Nice question, thanks!

I'm surprised after all the recent questions that were related that we still have ~25% answering this one incorrectly.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33632 Visits: 18560
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

Michael Riemer
Michael Riemer
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2855 Visits: 639
Nice question on the basics and all the ensuing explanations - thanks
VM-723206
VM-723206
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 267
Nice question.
zymos
zymos
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 259
Great question and pick on the data types. It reminds that by default '1' is assigned to non-defined variable declaration for the char & varchar data types.

Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search