How to remove last two number ending after float point SQL server 2012?

Question

I work on SQL server 2012 and I need to remove Last Two digit After Float Point

table already have big data and this is sample as below

max number after float point is 6 and at least one number after float point

if i have one digit After Float Point nothing do

So How to do that ?

create table #temp
(
Numbers float
)
insert into #temp(Numbers)
values
(155.7865),
(708.986517),
(200.333),
(555.3)

select * from #temp

Expected Result

Numbers
155.78
708.9865
200.3
555.3

What I have tried:

select round(Numbers,2),* from #temp

Answer 1

Hi @ahmed salah ,

Please refer below:

  ;with cte as (  
   select CONVERT(VARCHAR(50), Numbers,128) Numbers from #temp)  
    select   
 case when LEN(Numbers)-CHARINDEX('.',Numbers)>2 then Convert(float,left(Numbers,LEN(Numbers)-2))  
      when LEN(Numbers)-CHARINDEX('.',Numbers)=2 then Convert(float,left(Numbers,LEN(Numbers)-1))  
  else Convert(float,Numbers) end Numbers  
  from cte  

Output:
Numbers
155. 78
708. 9865
200. 3
555. 3

Or still using round:

  ;with cte as (  
    select Numbers Number,CONVERT(VARCHAR(50), Numbers,128) Numbers from #temp)  
  
  select case when len(Numbers)-CHARINDEX('.',Numbers)>2 then Convert(float,ROUND(Numbers,len(Numbers)-CHARINDEX('.',Numbers)-2))  
              when len(Numbers)-CHARINDEX('.',Numbers)=2 then Convert(float,ROUND(Numbers,len(Numbers)-CHARINDEX('.',Numbers)-1))  
			  else Convert(float,Numbers) end Numbers  
  from cte  

Output:
Numbers
155.79
708.9865
200.3
555.3

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

What data type do you want to return? This is a very important question, and you shouldn't look for a solution before you have answered that question.

The only class of data types where you can talk of having a "format" are the string types. For all other types, SQL server returns something binary to the client and it is the client that formats that binary data so it is readable for the human eye.

So if you are saying that you want to return a string, then sure, go ahead. We can try to discuss something here. Returning something else and yuo are at the mercy of the client application regarding the format.

But there is a deeper problem. A float isn't an exact numeric representation. Consider the number 3.1. SQL Server cannot represent that value as a float. So it represents something which is very near that value. Execute below both in SSMS and SQLCMD:

SELECT CAST(3.1 AS float)

In SSMS you will see a result something like 3.1. In SQLCMD, you'll see something like: 3.1000000000000001.

You have two tools, each giving a representation of this float value. How would you want that value in your output? Talking about removing the last two digits for something which isn't precise in the first place is something very strange to do.

In general, we recommend that the formatting should be done by the client application. In this case, I also urge you to consider the impreciseness aspect of the float type.

Answer 3

data type i need to return is Float

Answer 4

@ahmed salah ,

You are asking about decimal precision.
As @tibor_karaszi@hotmail.com pointed out, the FLOAT is imprecise data type. Its use should be minimized for the very few special cases.

For example, Pi (the ratio of a circle's circumference to its diameter) or e (Natural logarithm). Both numbers can go forever.

• 3.14 (or is it 3.14159...)
• 2.71 (or is it 2.71281828459...)

I would suggest to change your data type to DECIMAL(...)

Answer 5

Note that the selected answer assumes that you use SQL Server Management Studio, along with the rules it has for presentation of that data. Try that very same code using SQLCMD and you will see a very different result. As has been communicated, don't use imprecise types if you expect some certain representation of the value. Again, run it in SQLCMD and you will see what I mean. And re-read my earlier replies!