LEN and CONVERT

Question

Post reply

LEN and CONVERT

kapil_kk

SSC-Insane

Points: 21316
More actions
July 29, 2013 at 10:04 pm

#278146

Comments posted to this topic are about the item LEN and CONVERT
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

sharath.chalamgari

SSCertifiable

Points: 5680

July 29, 2013 at 11:30 pm

#1636829

simple one

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Nice one, thanks.

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 3

good information for me.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 4

Helps to read the question carefully - great easy question though - thanks.

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 5

I think that the answer has missed a point in not mentioning that the default precision of a decimal variable is decimal(18,0). It's the fact that there is no decimal part stored that means the length of statement 2 returns 5.

Philip

udayroy15 Ten Centuries Points: 1076 More actions · Answer 6

udayroy15

Ten Centuries

Points: 1076

July 30, 2013 at 2:38 am

#1636873

good question

thierry.vandurme Hall of Fame Points: 3340 More actions · Answer 7

sorry if I find the outcome not as trivial as some of you do :blush:

I don't understand the result for select 1. The money data type has a scale of 4. Why does SQL add .00 to it instead of .0000? I can't find any reference in bol stating "the default scale is 2".

Maybe someone can shed some light?

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 8

thierry.vandurme (7/30/2013)
sorry if I find the outcome not as trivial as some of you do :blush:
I don't understand the result for select 1. The money data type has a scale of 4. Why does SQL add .00 to it instead of .0000? I can't find any reference in bol stating "the default scale is 2".
Maybe someone can shed some light?

Thierry,

I hadn't really thought about it. After quite a bit of searching I discovered the following extract on MSDN http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx

In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Style=0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

Style=1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

Style=2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

Hope that clarifies things a bit.

Philip

thierry.vandurme Hall of Fame Points: 3340 More actions · Answer 9

Hi Philip

that indeed explains it. Thx a lot! I was almost losing my mind over this 🙂

Chrz

Thierry

psingla Hall of Fame Points: 3840 More actions · Answer 10

Nice Question

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Nice question. Pity there was no reference in the explanation to anything that supported the answer 12 rather than 14, but as it's shown up in the discussion I guess it doesn't matter.

Putting convert in the title was perhaps a pity, made it too easy as it meant no-one would struggle to work out what storage lengths were instead of string lengths; even with convert in the title 8,5,5 might have been a good distractor, and not having t made it even more unlikely that anyone would fall into that mistake.

Tom