+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Truncate (not round) decimal places in SQL Server (/Thread-Truncate-not-round-decimal-places-in-SQL-Server)
Truncate (not round) decimal places in SQL Server - lustrous694 - 07-31-2023
I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example:
declare @value decimal(18,2)
set @value = 123.456
This will automatically round `@value` to be `123.46`, which is good in most cases. However, for this project, I don't need that. Is there a simple way to truncate the decimals I don't need? I know I can use the `left()` function and convert back to a decimal. Are there any other ways?
RE: Truncate (not round) decimal places in SQL Server - cochampionswlygd - 07-31-2023
select convert(int,@value)
RE: Truncate (not round) decimal places in SQL Server - jozefn - 07-31-2023
Another truncate with no rounding solution and example.
Convert 71.950005666 to a single decimal place number (71.9)
1) 71.950005666 * 10.0 = 719.50005666
2) Floor(719.50005666) = 719.0
3) 719.0 / 10.0 = 71.9
select Floor(71.950005666 * 10.0) / 10.0
RE: Truncate (not round) decimal places in SQL Server - functors13162 - 07-31-2023
`Mod(x,1)` is the easiest way I think.
RE: Truncate (not round) decimal places in SQL Server - sabermeuijk - 07-31-2023
Here's the way I was able to truncate and not round:
select 100.0019-(100.0019%.001)
returns 100.0010
And your example:
select 123.456-(123.456%.001)
returns 123.450
Now if you want to get rid of the ending zero, simply cast it:
select cast((123.456-(123.456%.001)) as decimal (18,2))
returns 123.45
RE: Truncate (not round) decimal places in SQL Server - cate251441 - 07-31-2023
Please try to use this code for converting 3 decimal values after a point into 2 decimal places:
declare @val decimal (8, 2)
select @val = 123.456
select @val = @val
select @val
The output is 123.46
RE: Truncate (not round) decimal places in SQL Server - whydah395 - 07-31-2023
Round has an optional parameter
Select round(123.456, 2, 1) will = 123.45
Select round(123.456, 2, 0) will = 123.46
RE: Truncate (not round) decimal places in SQL Server - blanchette590 - 07-31-2023
SELECT Cast(Round(123.456,2,1) as decimal(18,2))
RE: Truncate (not round) decimal places in SQL Server - oralsbbzoryopf - 07-31-2023
I think you want only the decimal value,
in this case you can use the following:
declare @val decimal (8, 3)
SET @val = 123.456
SELECT @val - ROUND(@val,0,1)
RE: Truncate (not round) decimal places in SQL Server - dibase975163 - 07-31-2023
This will remove the decimal part of any number
SELECT ROUND(@val,0,1)