Truncating Dates

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:

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

,      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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s