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 =?
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 =?
I can't follow your example, 25*7.7634 +25 = 219.085 and that's what I get in SQL.
when u calculate by sql and convert to float then convert to money, it become 219.09
how to return 219.08
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).
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.
I would like to take 2 decimal without rounding
,how to do that?
I need 219.08
To truncate the value, try the ROUND function with three arguments like in this example:
select round( $219.0850, 2, 1)
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".
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.what does the last parameter meaning ? why input 1?
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.
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.
25 people are following this question.