L' Eomot Inversé (7/30/2013)
...... The question is about the lengths of the strings to which the numeric values are implicitly converted, since the length operator used is LEN not DATALENGTH while the function which delivers storage size is DATALENGTH, not LEN.
On the other hand, how would that explain this result I get (with SQL 2005)?
I set a "money" variable to a value with four decimal places, the SELECT of that variable returns all digits, yet the value returned by the LEN() function is two short.
declare @var money;
set @var = 12345.1234;
select @var, len(var);
-- returns: 12345.1234 8
LEN takes a string_expression parameter (http://msdn.microsoft.com/en-us/library/ms190329%28v=sql.90%29.aspx) and so SQL has to do an implicit conversion on the money variable in order to work out the length.
The default style when converting money to string is 0 which means No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point;
LEN(@var) is equivalent to
LEN(CONVERT(VARCHAR(50),@var,0) which means your money variable gets converted to the string '12345.12' with a length of 8.
The other style options are 1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point so
LEN(CONVERT(VARCHAR(50),@var,1) would give '12,345.12' with a length of 9; and 2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point which would give
LEN(CONVERT(VARCHAR(50),@var,2) returning '12345.1234' with a length of 10.
The whole confusion over this appears to do with the fact that LEN returns the length of a string, requiring an implicit conversion when the parameter is not a string.