TRUNC

TRUNC is one of those functions in Oracle you use and cast away probably without realising the true power available in the command.

Most programmers are aware that to 'TRUNC' a number removes all information to the right of the decimal point. So for example

SELECT TRUNC(2.7) FROM DUAL;


TRUNC(2.7)
----------
      2

1 row selected.

Some programmers are aware that you can TRUNC a date data type to return the same date with the time portion set to midnight (00:00:00).

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as NO_TRUNC ,
    TO_CHAR(TRUNC(SYSDATE),'DD/MM/YYYY HH24:MI:SS') as WITH_TRUNC
FROM DUAL;


NO_TRUNC            WITH_TRUNC      
------------------- -------------------
17/04/2007 10:35:00 17/04/2007 00:00:00

1 row selected.

And there most programmers knowledge of TRUNC stops. Probably because that is sufficient for most programming tasks that need to be taken care of. The other reason is, probably, that most programmers first encountered TRUNC while examining other people's code and thought that that was all there was to know about using this function. There is more to TRUNC than just this 'neat' trick. As with most inbuilt functions, TRUNC has been overloaded to accept additional arguments.

TRUNC no longer has to be wrapped in a SQL statement using the dual table but can also be use in direct variable assignments in PL/SQL as follows:

DECLARE
dt   DATE := TRUNC (SYSDATE);
BEGIN
NULL;
END;

Not the most elegant of code, but it does illustrate the point.

When dealing with number data types the additional argument is the number of decimal places to TRUNC to. Note that changing the number to negative replaces numbers to the left of the decimal separator with zeros.

SELECT TRUNC (77.77, 1) AS pos, TRUNC (77.77, -1) AS neg
FROM DUAL;


    POS        NEG
---------- ----------
   77.7         70

1 row selected.

For date data types TRUNC has been overloaded to accept a format mask. If you are familiar with casting date data types to their character equivalents using the TO_CHAR function you should be aware of format masks. The format masks I tend to use most are 'MM' for month and 'YYYY' for year, which give you the first day of the month and first day of the year respectively.

SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'DD-MON-YYYY') AS mon,
    TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'DD-MON-YYYY') AS yr
FROM DUAL;


MON         YR      
----------- -----------
01-APR-2007 01-JAN-2007

1 row selected.

So there you have it, some hidden functionality of a simple inbuilt function revealed to you. No doubt there may be more available features in later releases of Oracle, but for now 99% of my coding and SQL needs have been satisfied.

Remember that even the most innocuous of commands may have hidden features. Features that are only hidden in the manual waiting for you to discover them.