question

nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 asked LiHongMSFT-3908 answered

sql - calculate with decimal problem

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 =?

sql-server-generalsql-server-transact-sqlsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I can't follow your example, 25*7.7634 +25 = 219.085 and that's what I get in SQL.

0 Votes 0 ·
nononame2021-4205 avatar image
0 Votes"
nononame2021-4205 answered TomPhillips-1744 edited

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

how to return 219.08

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Are you saying you want 219.085 not rounded, but truncated to 219.08?

What about 219.089, do you want that 219.08 also?

PS. Money data type has 4 decimal places. So casting the value to Money is not causing the rounding. You must be casting it to decimal(12,2).


0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered nononame2021-4205 commented

Still can't understand/reproduce your issue:

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

157913--temp.png

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


-temp.png (7.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would like to take 2 decimal without rounding
,how to do that?

I need 219.08

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered OlafHelper-2800 commented

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

select round( $219.0850, 2, 1)




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

what does the last parameter meaning ? why input 1?

0 Votes 0 ·

According to public documentation, when the last argument "... is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated".


0 Votes 0 ·

what does the last parameter meaning ? why input 1?

All SQL functions are very well documented, simply look it up: [ROUND (Transact-SQL) ( https://docs.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver15) => When a value other than 0 is specified, numeric_expression is truncated.
0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @nononame2021-4205,

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.






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi,@nononame2021-4205
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.