SQL Query Result Problem

Question

I have Two SQL Query Both Return

select round(convert(float,'24367.723'),2)

Result:24367.72

Second:

select convert(varchar(20),round(convert(float,'24367.723'),2))

Result:24367.7

Why the Second Query Return exclude the last digit after converting to varchar

Thanks in Advance

Answer 1

By not specifying a style parameter to the convert function you get the default style (0).

i.e. it is equivalent to doing

select convert(varchar(20),round(convert(float,'24367.723'),2), 0)      

The default style for converting from float to varchar displays a maximum of 6 digits.

Answer 2

When working with a float the STR() function usually gives better results according to MSDN as you've more control.

E.g.

select str(convert(float,'24367.723'),8, 2)

Answer 3

Dont use floats, use exact numberics. Something like this

   convert(varchar(20), convert(numeric(20,2), '24367.72'))