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.2But i want it to display as
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.
This can be achieved with the
See below for an example:
declare @num as float; set @num=5.20; select convert(decimal(10, 2), @num);
The output here will be
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
decimalis more than sufficient.
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).