Most developers using Oracle will know of the TRUNC function and how, when applied to a DATE data type, it removes (TRUNCates) any time component and sets it to “0”, which is midnight (also considered the start of the day):
ALTER SESSION SET nls_date_format = 'dd-Mon-yyyy hh24:mi:ss' / SELECT SYSDATE AS date_time , TRUNC(SYSDATE) AS date_only FROM dual /
which gives us:
DATE_TIME DATE_ONLY ------------------- ------------------- 03/07/2012 07:02:48 03/07/2012 00:00:00
What many developers don’t appear to know is that the TRUNC function accepts a second parameter, which specifies the level of precision to which the date is truncated. Of course, a casual read of the Oracle SQL documentation will reveal this but I guess without the knowledge that TRUNC has a second parameter most developers simply overlook it.
So, using a time of 4:15:32 pm on 12 June 2012, the listing below shows some of the different ways there are to apply the TRUNC function:
| Precision | Parameter | Example |
|---|---|---|
| Year | YY | 01-JAN-2012 00:00:00 |
| Quarter | Q | 01-APR-2012 00:00:00 |
| Month | MM | 01-JUN-2012 00:00:00 |
| Week | DAY | 10-JUN-2012 00:00:00 |
| Day | DD | 12-JUN-2012 00:00:00 |
| Hour | HH | 12-JUN-2012 16:00:00 |
| Minute | MI | 12-JUN-2012 16:15:00 |
There are a few other TRUNC parameters available too. Naturally, there’s no option for truncating to the nearest second as that is the maximum level of precision of the DATE data type.
You may also note that the same options apply to the ROUND function.
Another potentially important point to keep in mind is that the TRUNC function operates on DATE data types. If you apply it to a TIMESTAMP data type then Oracle will implicitly convert your TIMESTAMP to a DATE, apply the TRUNC function and return a DATE data type. We can see this by using the DUMP function to display the underlying data type. You’ll note how in the following query, the data type is the same even though one was based on a TIMESTAMP and one on a DATE:
SELECT DUMP(TRUNC(SYSDATE)) AS dump_date , DUMP(TRUNC(SYSTIMESTAMP)) AS dump_timestamp FROM dual / DUMP_DATE DUMP_TIMESTAMP -------------------------------- -------------------------------- Typ=13 Len=8: 220,7,7,3,0,0,0,0 Typ=13 Len=8: 220,7,7,3,0,0,0,0
You can view the Oracle documentation for a complete listing of the codes for each data type.
The implicit conversion and return data type are particularly important to understand as they are the places where bugs may be introduced. Drawing the difference between two TIMESTAMPs returns an INTERVAL data type but drawing the difference between two DATEs returns a NUMBER data type.
Mark Hoxey 