3

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. – ToolmakerSteve Mar 31 at 20:35
9

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.

| improve this answer | |
  • 2
    +1 ...and most people also get surprised by funny things in float – Aaron Bertrand Jan 9 '14 at 15:30
  • 2
    And just for an exclamation point: DON'T USE FLOAT UNLESS YOU ARE ABSOLUTELY SURE YOU NEED TO. – Aaron Bertrand Jan 9 '14 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! – ToolmakerSteve Mar 31 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? – Aaron Bertrand Apr 1 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. – ToolmakerSteve Apr 1 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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