In Oracle Database, the TRUNC(number) function returns a given number, truncated to a given number of decimal places.

Oracle also has a TRUNC(date) function, which is used on dates. This article is solely about the TRUNC(number) function, which is used on numbers.

Syntax

The syntax goes like this:

TRUNC(n1 [, n2 ])

Where n1 is the value to truncate, and n2 is an optional argument that specifies how many decimal places to truncate n1 to. If n2 is omitted, then n1 is truncated to zero decimal places.

n1 can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Example

Here’s an example:

SELECT TRUNC(3.95)
FROM DUAL;

Result:

   TRUNC(3.95) 
______________ 
             3

Specify a Decimal Place

Here’s an example of passing a second argument to specify how many decimal places to truncate the value to:

SELECT TRUNC(1.25817, 2)
FROM DUAL;

Result:

   TRUNC(1.25817,2) 
___________________ 
               1.25

Here are some more:

SELECT 
    TRUNC(1.25817, 1) AS "1",
    TRUNC(1.25817, 2) AS "2",
    TRUNC(1.25817, 3) AS "3",
    TRUNC(1.25817, 4) AS "4"
FROM DUAL;

Result:

     1       2        3         4 
______ _______ ________ _________ 
   1.2    1.25    1.258    1.2581 

Negative Decimal Places

The second argument can be a negative value if required. Passing a negative value causes digits to the left of the decimal place to become zero.

Example:

SELECT TRUNC(6973.45, -2)
FROM DUAL;

Result:

   TRUNC(6973.45,-2) 
____________________ 
                6900

Compared to ROUND()

The TRUNC() function is different to the ROUND() function. The ROUND() function rounds the number up in some instances and down in others. The TRUNC() function, on the other hand, simply truncates the number without rounding.

Here’s a comparison to demonstrate this difference:

SELECT 
    TRUNC(3.6789, 2),
    ROUND(3.6789, 2)
FROM DUAL;

Result:

   TRUNC(3.6789,2)    ROUND(3.6789,2) 
__________________ __________________ 
              3.67               3.68 

It’s also different to the FLOOR() function, which returns the largest integer equal to or less than its argument. FLOOR() doesn’t accept a second argument like ROUND() and TRUNC() do (it only ever returns an integer anyway).

Null Values

If any argument is null, the result is null:

SET NULL 'null';

SELECT 
    TRUNC(null, 2),
    TRUNC(2.35, null),
    TRUNC(null, null)
FROM DUAL;

Result:

   TRUNC(NULL,2)    TRUNC(2.35,NULL)    TRUNC(NULL,NULL) 
________________ ___________________ ___________________ 
            null                null                null 

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Non-Numeric Arguments

Here’s what happens when we provide non-numeric arguments:

SELECT TRUNC('Hundred', 'Two')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRUNC('Hundred', 'Two')
FROM DUAL
Error report -
ORA-01722: invalid number

Invalid Argument Count

Calling TRUNC() with the wrong number of arguments, or without an argument results in an error:

SELECT TRUNC()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRUNC()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And:

SELECT TRUNC(1, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TRUNC(1, 2, 3)
FROM DUAL
Error at Command Line : 1 Column : 20
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: