Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DATALENGTH Expand / Collapse
Author
Message
Posted Monday, August 23, 2010 10:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 1, 2016 5:05 AM
Points: 1,683, Visits: 1,800
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
Post #973622
Posted Monday, August 23, 2010 11:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 18, 2016 8:42 AM
Points: 3,375, Visits: 1,323
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.



Post #973627
Posted Monday, August 23, 2010 2:13 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 30, 2015 9:47 AM
Points: 2,160, Visits: 2,204
Nice question, thanks!

I'm surprised after all the recent questions that were related that we still have ~25% answering this one incorrectly.
Post #973725
Posted Monday, August 23, 2010 3:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 20,082, Visits: 18,256
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
Post #973755
Posted Tuesday, August 24, 2010 3:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 24, 2016 1:31 AM
Points: 2,587, Visits: 621
Nice question on the basics and all the ensuing explanations - thanks
Post #973956
Posted Tuesday, August 24, 2010 9:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 AM
Points: 654, Visits: 267
Nice question.
Post #974202
Posted Wednesday, March 7, 2012 2:01 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 17, 2015 3:03 PM
Points: 504, 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.
Post #1263271
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse