In Oracle Database, the TRUNC(date) function returns the given date value with the time portion of the day truncated to the unit provided in the specified format model.

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

Syntax

The syntax goes like this:

TRUNC(date [, fmt ])

Where date is the date to truncate, and fmt is an optional format model that specifies a unit for which to truncate the value. The format model can be any of the supported format models for the TRUNC(date) and ROUND(date) functions.

The function operates according to the rules of the Gregorian calendar (it’s not sensitive to the value of the NLS_CALENDAR parameter).

Example

Here’s an example:

SELECT TRUNC(DATE '2035-08-22', 'MONTH')
FROM DUAL;

Result:

01-AUG-35

Here are some more:

SELECT 
    TRUNC(DATE '2035-08-22', 'D') AS "D",
    TRUNC(DATE '2035-08-22', 'DD') AS "DD",
    TRUNC(DATE '2035-08-22', 'MONTH') AS "Month",
    TRUNC(DATE '2035-08-22', 'YEAR') AS "Year",
    TRUNC(DATE '2035-08-22', 'CC') AS "CC"
FROM DUAL;

Result:

           D           DD        Month         Year           CC 
____________ ____________ ____________ ____________ ____________ 
19-AUG-35    22-AUG-35    01-AUG-35    01-JAN-35    01-JAN-01   

The result is formatted according to the date format for the current session. See How to Check the Date Format of the Current Session and How to Change the Date Format for the Current Session for more information about that.

Default Date Unit

The default date unit is DD:

SELECT 
    TRUNC(DATE '2035-08-22') AS "Default",
    TRUNC(DATE '2035-08-22', 'DD') AS "DD"
FROM DUAL;

Result:

     Default           DD 
____________ ____________ 
22-AUG-35    22-AUG-35   

Compared to ROUND()

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

Here’s a comparison to demonstrate this difference:

SELECT 
    TRUNC(DATE '2030-12-31', 'MONTH') AS "Trunc",
    ROUND(DATE '2030-12-31', 'MONTH') AS "Round"
FROM DUAL;

Result:

       Trunc        Round 
____________ ____________ 
01-DEC-30    01-JAN-31   

Null Values

If either argument is null, the result is null:

SET NULL 'null';
SELECT 
    TRUNC(null, 'D'),
    TRUNC(date'2020-12-30', null)
FROM DUAL;

Result:

   TRUNC(NULL,'D')    TRUNC(DATE'2020-12-30',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.

Invalid Argument Count

Calling TRUNC() 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 calling it with too many arguments results in an error:

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: