The TRUNC() function

ON NUMBERS
This function allows to make two types of action on a number:
- Keep the number of wished decimals (precision > = 0)
- Centre the whole part of a number in a range (precision <>
------------------------------------
select
trunc(12.98764, 5) "+5 dec."
trunc(12.98764, 4) "+4 dec."
trunc(12.98764, 3) "+3 dec."
trunc(12.98764, 2) "+2 dec."
trunc(12.98764, 1) "+1 dec."
trunc(12.98764, 0) "+0 dec."
from dual ;

+5 dec. +4 dec. +3 dec. +2 dec. +1 dec. +0 dec.
---------- ---------- ---------- ---------- ---------- ----------
12,98764 12,9876 12,987 12,98 12,9 12
---------------------------------------------------------------

select
trunc(1234567,-6) "million"
trunc(1234567,-5) "cent-mille"
trunc(1234567,-4) "dix-mille"
trunc(1234567,-3) "mille"
trunc(1234567,-2) "cent"
trunc(1234567,-1) "dix" from dual ;

million cent-mille dix-mille mille cent dix
---------- ---------- ---------- ---------- ---------- ----------
1000000 1200000 1230000 1234000 1234500 1234560
--------------------------------------------------------------------


ON DATE
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;
SQL> SELECT SYSDATE "Current date" FROM DUAL ;

Curent date
-------------------
08/10/2004 14:08:48

-- date truncated to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;

'YEAR'
-------------------
01/01/2004 00:00:00

-- date truncated to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;

'IYYY'
-------------------
29/12/2003 00:00:00

-- date truncated to the 1st day of the quarter --
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;

'Q'
-------------------
01/10/2004 00:00:00

-- date truncated to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;

'MONTH'
-------------------
01/10/2004 00:00:00

-- date truncated to the 1st day of the week --
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'
-------------------
04/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'W') "'W'" FROM DUAL;

'W'
-------------------
08/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'WW') "'WW'" FROM DUAL;

'WW'
-------------------
07/10/2004 00:00:00

-- day of the week corresponding to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IW') "'IW'" FROM DUAL;

'IW'
-------------------
04/10/2004 00:00:00

-- date truncated to the day (suppress hours) --
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;

'DD'
-------------------
08/10/2004 00:00:00

-- date truncated to the hour (suppress minutes) --
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;

'HH'
-------------------
08/10/2004 14:00:00

-- date truncated to the minute (suppress seconds) --
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;

'MI'
-------------------
08/10/2004 14:08:00