The TRUNC() function

18.10.2005

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 < 0)

Example of preservation of the decimal part

<pre>SQL> select
2 trunc(12.98764, 5) "+5 dec."
3 ,trunc(12.98764, 4) "+4 dec."
4 ,trunc(12.98764, 3) "+3 dec."
5 ,trunc(12.98764, 2) "+2 dec."
6 ,trunc(12.98764, 1) "+1 dec."
7 ,trunc(12.98764, 0) "+0 dec."
8 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

SQL></pre>

Example of centring by ranges

<pre>SQL> select
2 trunc(1234567,-6) "million"
3 ,trunc(1234567,-5) "cent-mille"
4 ,trunc(1234567,-4) "dix-mille"
5 ,trunc(1234567,-3) "mille"
6 ,trunc(1234567,-2) "cent"
7 ,trunc(1234567,-1) "dix"
8 from dual ;

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

SQL></pre>

Extraction of the entire and decimal parts of a number

<pre>SQL> DECLARE
2    LN$Num number := 100.95 ;
3 BEGIN
4    DBMS_OUTPUT.PUT_LINE( 'Entire part : ' || To_char( TRUNC ( LN$Num ) )) ;
5    DBMS_OUTPUT.PUT_LINE( 'Decimal part : ' || To_char( LN$Num - TRUNC (LN$Num ) ) ) ;
6 END ;
7 /
Entire part : 100
Decimal part : ,95

Procédure PL/SQL terminée avec succès.

SQL></pre>

ON DATES

<pre>SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;

Session modifiée.

SQL>
SQL> -- Current date --
SQL> SELECT SYSDATE "Current date" FROM DUAL ;

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

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

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

SQL>
SQL> -- 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

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

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

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

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

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

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

SQL>
SQL> -- 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

SQL>
SQL> -- 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

SQL>
SQL> -- 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

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

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

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

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

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

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

SQL></pre>

Francois