In sql server I’m trying to truncate a number to 4 digits after the coma. From what I know I need to use Round(n, b, f). n should be the number I want to round, b is how many digits after the coma should be left and if f is 0 then it works like a normal Round but if it’s not 0 then it should Truncate after the digits specified in b. But it doesn’t seem to work for me.
Here is the code
SELECT ROUND(SUM(LAT_N), 4, 1)
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345
It should sum all the lat_n that are bigger than 38.7880 and smaller than 137.2345 and truncate the sum to 4 digits. But it gives me this result: 36354.81350000 instead of 36354.8135. I tried with CAST this way:
SELECT CAST(ROUND(SUM(LAT_N), 4) AS DECIMAL(8, 4)
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345
But it didn’t work giving me this error Arithmetic overflow error converting numeric to data type numeric. I’ve had to do similar stuff before with only 2 decimals and Cast with Decimal(8, 2) used to work. Can someone explain to me what I’m doing wrong with the Cast or maybe Round isn’t what I need?
>Solution :
36354.8135 won’t fit in decimal(8,4). If you want numbers > 9999 and you want four decimal places, you’ll need more than 8 for precision.
Try:
SELECT CONVERT(decimal(20,4), 36354.8135);