Float datatype with 2 digits after decimal point

  • 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.

  • 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.

    +1 ...and most people also get surprised by funny things in float

    And just for an exclamation point: DON'T USE FLOAT UNLESS YOU ARE ABSOLUTELY SURE YOU NEED TO.

    @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 That's cool in a textbook. Can you demonstrate a practical and measurable performance difference after choosing between float or decimal in SQL Server?

    @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 You are free to favor the application (where you can convert data to float types when you are using them for calculations there and can demonstrate a performance benefit of doing so), but I will continue to favor the database (where you will be hard pressed to demonstrate a performance value) and the users (because float causes a never-ending slew of questions).

    @AaronBertrand - thanks, I appreciate that perspective. I will keep in mind the possibility that decimal is a less problematic format for persisting non-integer values, as you point out, here and elsewhere. I might not be giving that enough weight.

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM