198

I would like to know how can I output a number with 2 decimal places, without rounding the original number.

For example:

2229,999 -> 2229,99

I already tried:

FORMAT(2229.999, 2)
CONVERT(2229.999, DECIMAL(4,2))
radbyx
  • 9,352
  • 21
  • 84
  • 127
Tenza
  • 2,301
  • 2
  • 16
  • 18
  • it looks like you are trying to use commands to truncate. Why not just use the built in funcationality? – Fallenreaper Jun 25 '12 at 13:59
  • Don't you need the decimal to be `DECIMAL(6,2))`. 6- Precision ( number of total digits) and 2 is the number of digits after decimal ? – user5249203 Feb 16 '16 at 17:55

6 Answers6

421

When formatting number to 2 decimal places you have two options TRUNCATE and ROUND. You are looking for TRUNCATE function.

Examples:

Without rounding:

TRUNCATE(0.166, 2)
-- will be evaluated to 0.16

TRUNCATE(0.164, 2)
-- will be evaluated to 0.16

docs: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate

With rounding:

ROUND(0.166, 2)
-- will be evaluated to 0.17

ROUND(0.164, 2)
-- will be evaluated to 0.16

docs: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_round

General Grievance
  • 4,555
  • 31
  • 31
  • 45
jmarceli
  • 19,102
  • 6
  • 69
  • 67
  • 3
    you should use `truncate` to format numbers when you want truncated numbers. she made it quite clear in the question that this is what she wanted to do. there are many ways of rounding (floor, ceiling, away-from-zero, toward-zero, half-up, half-down, half-even). they are appropriate in different contexts. – Kip Jul 15 '14 at 18:00
  • 23
    yeah of course, but as I can see many people find that my answer was helpful or maybe they found this question when they were looking for ROUND command. I will leave it as it is. – jmarceli Jul 15 '14 at 19:21
  • _"you shouldn't use TRUNCATE to format numbers because you will simply lose truncated digits"_ => Well, that's the point of truncature... I don't see how we should get 0.17 with Math. Your answer is perfect for programming examples but it is not very thorough. – Benoit Duffez Dec 18 '14 at 07:52
  • 5
    As I said before. I KNOW that it is not a correct answer for this question but people tend to like it :) Maybe because of the question title which could be the search result for someone who is looking for the `ROUND` function. – jmarceli Dec 18 '14 at 09:25
  • Then you should fix it. I will still get useful for everyone, _and_ it will be 100% correct. Win-win :) – Benoit Duffez Dec 22 '14 at 09:21
  • is this rounding `ROUND` safe to use with money values? – Renato Gama Sep 11 '15 at 14:14
  • Truncate was working fine for me until I saw this issue. When I truncate 00.01 then it is giving me 00.01 which is correct. But when I do :- Actual amount: 20574.01 After Truncate: 20574.00 Why so? – Gaurav Singhal Jan 05 '18 at 17:45
  • Given 5 or 5.5 this does not "*output a number with 2 decimal places*" which is what the question asks. – But those new buttons though.. Jul 16 '18 at 10:32
  • I do approve the fact you have put in ROUND also. It is the correct way to do it so it should be mentioned! – Nebulosar Oct 17 '18 at 13:28
91

You want to use the TRUNCATE command.

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_truncate

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
jasonlfunk
  • 5,159
  • 4
  • 29
  • 39
  • 10
    Truncate always "rounds" down. 1.999 truncated to 2 DP would be 1.99 where 2.00 would mathematically be more correct. If that's not a problem then truncate's fine but you should be aware of it. – GordonM Apr 24 '15 at 15:36
  • I agree to GordonM and TRUNCATE should be used with causion, especially with floating point numbers! – Nebulosar Oct 17 '18 at 13:27
20

How about CAST(2229.999 AS DECIMAL(6,2)) to get a decimal with 2 decimal places

Hituptony
  • 2,740
  • 3
  • 22
  • 44
  • 5
    I think you mean cast(2229.999 as decimal(4,2)) – Uncle Iroh Apr 24 '14 at 20:32
  • 3
    @Pacerier - Both work technically. However the format for convert is convert(2229.999, decimal(4,2)) with a comma, so you're probably right in that that was what he was going for. – Uncle Iroh Apr 28 '15 at 15:23
  • @UncleIroh, Ah yes, my typo above. I was talking about `convert(2229.999, decimal(4,2))`. – Pacerier May 24 '15 at 15:55
  • 6
    CAST(2229.999 AS DECIMAL(4,2)) will give you 99.99. DECIMAL is the maximum number of digits **including** the 2 to the left of the decimal place. CAST(2229.99 AS DECIMAL(6,2)) will give you 2230.00 – russjohnson09 Apr 22 '16 at 16:58
16

Just use

format(number, qtyDecimals)
sample: format(1000, 2)
result 1000.00
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
Alessandro Garcia
  • 198
  • 1
  • 6
2

This is how I used this is as an example:

CAST(vAvgMaterialUnitCost.`avgUnitCost` AS DECIMAL(11,2)) * woMaterials.`qtyUsed` AS materialCost
Tye Lucas
  • 107
  • 1
  • 1
  • 9
1

Show as decimal Select ifnull(format(100.00, 1, 'en_US'), 0) 100.0

Show as Percentage Select concat(ifnull(format(100.00, 0, 'en_US'), 0), '%') 100%

Gregory Bologna
  • 270
  • 1
  • 6
  • 20