Truncates the input value of number to the specified decimal place.
Usage notes
If the Hive-compatible data type edition is used, this function is not a mathematical function. It is used to convert a date value. For more information about the related date function, see DATETRUNC. You must set the data type edition of your MaxCompute project based on your business requirements. For more information about data type editions, see Data type editions.
Syntax
double|decimal trunc(<number>[, bigint <decimal_places>])Parameters
number: required. A value of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
decimal_places: optional. A constant of the BIGINT type. This parameter specifies the decimal position where the number is truncated. If this parameter is not specified, the number is truncated to the ones place. decimal_places can be a negative value, which indicates that the number is truncated from the decimal point to the left and the decimal part is left out. If the value of decimal_places exceeds the length of the integer part, 0 is returned.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If decimal_places is not of the BIGINT type, an error is returned.
- If the value of number or decimal_places is null, null is returned.
If a value of the DOUBLE type is returned, the return value may not be properly displayed. This issue exists in all systems. For more information, see
trunc(125.815,1)in the following examples.The number is filled with zeros from the specified position.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. In this topic, a table named mf_math_fun_t is created and data is inserted into the table. Sample statements:
create table if not exists mf_math_fun_t(
int_data int,
bigint_data bigint,
double_data double,
decimal_data decimal,
float_data float,
string_data string
);
insert into mf_math_fun_t values
(null, -10, 0.525, 0.525BD, cast(0.525 as float), '10'),
(-20, null, -0.1, -0.1BD, cast(-0.1 as float), '-10'),
(0, -1, null, 20.45BD, cast(-1 as float), '30'),
(-40, 4, 0.89, null, cast(0.89 as float), '-30'),
(5, -50, -1, -1BD, null, '50'),
(-60, 6, 1.5, 1.5BD, cast(1.5 as float), '-50'),
(-1, -70, -7.5, -7.5BD, cast(-7.5 as float),null ),
(-80, 1, -10.2, -10.2BD, cast(-10.2 as float), '-1' ),
(9, -90, 2.58, 2.58BD, cast(2.58 as float), '0'),
(-100, 10, -5.8, -5.8BD, cast(-5.8 as float), '-90');Query data from the mf_math_fun_t table. Sample statement:
select * from mf_math_fun_t;
-- The following result is returned:
+------------+-------------+-------------+--------------+------------+-------------+
| int_data | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL | -10 | 0.525 | 0.525 | 0.525 | 10 |
| -20 | NULL | -0.1 | -0.1 | -0.1 | -10 |
| 0 | -1 | NULL | 20.45 | -1.0 | 30 |
| -40 | 4 | 0.89 | NULL | 0.89 | -30 |
| 5 | -50 | -1.0 | -1 | NULL | 50 |
| -60 | 6 | 1.5 | 1.5 | 1.5 | -50 |
| -1 | -70 | -7.5 | -7.5 | -7.5 | NULL |
| -80 | 1 | -10.2 | -10.2 | -10.2 | -1 |
| 9 | -90 | 2.58 | 2.58 | 2.58 | 0 |
| -100 | 10 | -5.8 | -5.8 | -5.8 | -90 |
| NULL | -10 | 0.525 | 0.525 | 0.525 | 10 |
| -20 | NULL | -0.1 | -0.1 | -0.1 | -10 |
| 0 | -1 | NULL | 20.45 | -1.0 | 30 |
| -40 | 4 | 0.89 | NULL | 0.89 | -30 |
| 5 | -50 | -1.0 | -1 | NULL | 50 |
| -60 | 6 | 1.5 | 1.5 | 1.5 | -50 |
| -1 | -70 | -7.5 | -7.5 | -7.5 | NULL |
| -80 | 1 | -10.2 | -10.2 | -10.2 | -1 |
| 9 | -90 | 2.58 | 2.58 | 2.58 | 0 |
| -100 | 10 | -5.8 | -5.8 | -5.8 | -90 |
+------------+-------------+-------------+--------------+------------+-------------+Example: static data
-- The value 125.0 is returned.
select trunc(125.815,0);
-- The value 125.80000000000001 is returned.
select trunc(125.815,1);
-- The value 125.81 is returned.
select trunc(125.815,2);
-- The value 125.815 is returned.
select trunc(125.815,3);
-- The value -125.81 is returned.
select trunc(-125.815,2);
-- The value 120.0 is returned.
select trunc(125.815,-1);
-- The value 100.0 is returned.
select trunc(125.815,-2);
-- The value 0.0 is returned.
select trunc(125.815,-3);
-- The value 123.345 is returned.
select trunc(123.345,4);
-- The value 0.0 is returned.
select trunc(123.345,-4);
-- The value null is returned.
select trunc(123.345,null);Example: table data
Truncate numbers in a column to the specified decimal place based on the sample data. Sample statement:
select trunc(bigint_data, 1) as bigint_new, trunc(double_data,1) as double_new, trunc(decimal_data, 1) as decimal_new, trunc(string_data, 1) as string_new from mf_math_fun_t;The following result is returned:
+------------+---------------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+---------------------+-------------+------------+
| -10.0 | 0.5 | 0.5 | 10.0 |
| NULL | -0.1 | -0.1 | -10.0 |
| -1.0 | NULL | 20.4 | 30.0 |
| 4.0 | 0.8 | NULL | -30.0 |
| -50.0 | -1.0 | -1 | 50.0 |
| 6.0 | 1.5 | 1.5 | -50.0 |
| -70.0 | -7.5 | -7.5 | NULL |
| 1.0 | -10.200000000000001 | -10.2 | -1.0 |
| -90.0 | 2.5 | 2.5 | 0.0 |
| 10.0 | -5.800000000000001 | -5.8 | -90.0 |
+------------+---------------------+-------------+------------+Related functions
TRUNC is a mathematical function. For more information about functions related to data computing and conversion, see Mathematical functions.