In SQL server float datatype does not display trailing zeros after decimal point

declare @num as float
set @num=5.20
select @num

will dispaly only 5.2 But i want it to display as 5.20

After a little research i found that it can be solved by using

select CAST(@num AS numeric(10,2))

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?(It will be better if any variants are there in float which can do the same)

If it is possible then i need not change the SQL statement and include cast etc. So please help me on this.

  • The question conflates displaying a value with what is stored in a value. Whatever application language you use has formatting features to control "display"; it shouldn't matter how many digits you see when you display the value directly from SQL, hence seeing "default formatting". Rather, in application code (php, c#, javascript, ...), specify display format to use. Mar 31, 2020 at 20:35

1 Answer 1


This can be achieved with the decimal datatype.

See below for an example:

declare @num as float;
set @num=5.20;

select convert(decimal(10, 2), @num);

The output here will be 5.20.

After re-reading your question:

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?

Is there a reason why you are specifically using float? Oftentimes people tend to default to that datatype when decimal is more than sufficient.

  • 2
    +1 ...and most people also get surprised by funny things in float Jan 9, 2014 at 15:30
  • 2
    And just for an exclamation point: DON'T USE FLOAT UNLESS YOU ARE ABSOLUTELY SURE YOU NEED TO. Jan 9, 2014 at 15:54
  • @AaronBertrand - the downside of decimal is that it uses either 5 or 9 bytes for the commonly needed sizes. Float uses 4 or 8. Computers, both in memory usage and performance, deal better with byte sizes that are multiples of 2. I see "Decimal" as a more "specialized" data type. Though I certainly agree with you that anyone using floats must be aware of the subtleties of using them well! Mar 31, 2020 at 20:32
  • @ToolmakerSteve That's cool in a textbook. Can you demonstrate a practical and measurable performance difference after choosing between float or decimal in SQL Server? Apr 1, 2020 at 12:19
  • @AaronBertrand - in the server itself, no. But in the associated application software that the data is sent to, definitely. In my experience, data formats are driven primarily by applications' needs; I develop applications where float types are usually more relevant than decimal types, so I was surprised by your "decimal is more than sufficient" comment. I have the opposite mindset: float is usually "more than sufficient" (if used carefully), so I don't burden the cpu with decimal processing. My apologies, I should have clarified my POV. Apr 1, 2020 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.