sql - calculate with decimal problem

Question

I need to perform the calculations and want the output with 2 decimal

how can I take 2 decimal as money datatype without rounding by sql?

219.085 take 219.08

but now I save as money data type but it return 258.08 instead of 219.09?

25*7.7634 +25 =?

Answer 1

when u calculate by sql and convert to float then convert to money, it become 219.09

how to return 219.08

Answer 2

Still can't understand/reproduce your issue:

select cast(cast(25.0 * 7.7634 + 25.0 as float) as money)  

And when you round to 2 decimals, then 219.09 is the right result.

Answer 3

To truncate the value, try the ROUND function with three arguments like in this example:

 select round( $219.0850, 2, 1)

Answer 4

Hi @nononame2021 ,

Also try with below.

select CAST(round(val - 0.005, 2)  as numeric(18, 2))  

About round function in TSQL, you may refer round-transact-sql

Note that you have post several issues on Q&A, if the resolution is helpful, please mark it as answer so other user with similar problem could see this easier.

This behavior could also encourage our experts to offer more help.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 5

Hi，@nononame2021
Welcome to Microsoft T-SQL Q&A Forum!
Here are some methods for your reference:

DECLARE @v decimal(10,5)  
SET @v=25*7.7634+25  
--test1  
SELECT CONVERT(MONEY,ROUND(@v,2,1) )  
  
--test2  
SELECT CONVERT(MONEY,LEFT(@v,CHARINDEX('.',@v)+2))  
  
--test3  
SELECT CONVERT(MONEY,FLOOR(@v*100)/100)  

By the way,you can search the function in the official documentation.The documents explains very well.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.