truncate

description

Syntax

DOUBLE truncate(DOUBLE x, INT d)

Numerically truncate x according to the number of decimal places d.

The rules are as follows:

If d is literal:
When d > 0: keep d decimal places of x
When d = 0: remove the fractional part of x and keep only the integer part
When d < 0: Remove the fractional part of x, and replace the integer part with the number 0 according to the number of digits specified by d

Else if d is a column, and x has Decimal type, scale of result Decimal will always be same with input Decimal.

example

  1. mysql> select truncate(124.3867, 2);
  2. +-----------------------+
  3. | truncate(124.3867, 2) |
  4. +-----------------------+
  5. |                124.38 |
  6. +-----------------------+
  7. mysql> select truncate(124.3867, 0);
  8. +-----------------------+
  9. | truncate(124.3867, 0) |
  10. +-----------------------+
  11. |                   124 |
  12. +-----------------------+
  13. mysql> select truncate(-124.3867, -2);
  14. +-------------------------+
  15. | truncate(-124.3867, -2) |
  16. +-------------------------+
  17. |                    -100 |
  18. +-------------------------+
  19. mysql> select cast("123.123456" as Decimal(9,6)), number, truncate(cast ("123.123456" as Decimal(9,6)), number) from numbers("number"="5");
  20. --------------
  21. +---------------------------------------+--------+----------------------------------------------------------------------+
  22. | cast('123.123456' as DECIMALV3(9, 6)) | number | truncate(cast('123.123456' as DECIMALV3(9, 6)), cast(number as INT)) |
  23. +---------------------------------------+--------+----------------------------------------------------------------------+
  24. |                            123.123456 |      0 |                                                           123.000000 |
  25. |                            123.123456 |      1 |                                                           123.100000 |
  26. |                            123.123456 |      2 |                                                           123.120000 |
  27. |                            123.123456 |      3 |                                                           123.123000 |
  28. |                            123.123456 |      4 |                                                           123.123400 |
  29. +---------------------------------------+--------+----------------------------------------------------------------------+

keywords

  1. TRUNCATE