Skip to main content
Version: current [26.x]

Categories: Math

TRUNCATE

Truncates the input expression toward zero to the nearest integer or to the specified number of decimal places.

Syntax​

TRUNCATE(numeric_expression float) → int​

  • numeric_expression: The numeric expression to truncate.

Examples

TRUNCATE example 
SELECT TRUNCATE(987.65)
-- 987

TRUNCATE(numeric_expression double) → int​

  • numeric_expression: The numeric expression to truncate.

Examples

TRUNCATE example 
SELECT TRUNCATE(987.87)
-- 987

TRUNCATE(numeric_expression int32) → int32​

  • numeric_expression: The numeric expression to truncate.

Examples

TRUNCATE example 
SELECT TRUNCATE(2345, -1)
-- 2340

TRUNCATE(numeric_expression int64) → int64​

  • numeric_expression: The numeric expression to truncate.

Examples

TRUNCATE example 
SELECT TRUNCATE(CAST(98765 AS BIGINT), -2)
-- 98700

TRUNCATE(numeric_expression decimal(0,0) [, scale_expression int32]) → decimal(0,0)​

  • numeric_expression: The numeric expression to truncate.
  • scale_expression (optional): The number of decimal places to truncate to. Positive values truncate after the decimal point, negative values before.

Examples

TRUNCATE example 
SELECT TRUNCATE(89.2283211, 2)
-- 89.22

TRUNCATE(numeric_expression int64 [, scale_expression int32]) → int64​

  • numeric_expression: The numeric expression to truncate.
  • scale_expression (optional): The number of decimal places to truncate to. Positive values truncate after the decimal point, negative values before.

Examples

TRUNCATE example 
SELECT TRUNCATE(CAST(54321 AS BIGINT), -3)
-- 54000

TRUNCATE(numeric_expression decimal(0,0)) → decimal(0,0)​

  • numeric_expression: The numeric expression to truncate.

Examples

TRUNCATE example 
SELECT TRUNCATE(9.7)
-- 9

TRUNCATE(numeric_expression float [, scale_expression int32]) → float​

  • numeric_expression: The numeric expression to truncate.
  • scale_expression (optional): The decimal place to round to.

Examples

TRUNCATE example 
SELECT TRUNCATE(78.9823, 2)
-- 78.98

TRUNCATE(numeric_expression int32 [, scale_expression int32]) → int32​

  • numeric_expression: The numeric expression to truncate.
  • scale_expression (optional): The decimal place to round to.

Examples

TRUNCATE example 
SELECT TRUNCATE(4356, -2)
-- 4300

TRUNCATE(numeric_expression double [, scale_expression int32]) → double​

  • numeric_expression: The numeric expression to truncate.
  • scale_expression (optional): The decimal place to round to.

Examples

TRUNCATE example 
SELECT TRUNCATE(987.65, 1)
-- 987.6

Usage Notes​

To round to a place after the decimal point, specify the position as a positive number. To round to a place before the decimal point, specify the position as a negative number. To round to the nearest whole number, specify 0. If no decimal position is specified, then the function rounds to the nearest whole number (assumes 0 as the scale expression).