For me, TRUNC is one of the handiest Date functions available. If you work with time-based data a lot you’ll frequently find a home for it in your queries. For example, find all objects that were modified yesterday:
select * from all_objects where last_ddl_time >= trunc(sysdate)-1 and last_ddl_time < trunc(sysdate)
TRUNC isn’t limited to just daily resolution. You can truncate a Date value to the century, year, quarter, month, week, day, hour and minute. Weeks can be defined by NLS settings (‘D’, ‘DY’,’DAY’) , ISO weeks (‘IW’ – weeks start on Monday), or be based on start of year (‘WW’), or start of month (‘W’). As an example, this query:
SELECT 'cc', 'century', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'cc') FROM DUAL
UNION ALL
SELECT 'yy', 'year', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy') FROM DUAL
UNION ALL
SELECT 'q', 'quarter', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'q') FROM DUAL
UNION ALL
SELECT 'mm', 'month', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'mm') FROM DUAL
UNION ALL
SELECT 'iw', 'ISO week', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'iw') FROM DUAL
UNION ALL
SELECT 'ww', 'NLS_TERRITORY week', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'ww') FROM DUAL
UNION ALL
SELECT 'dd', 'day', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'dd') FROM DUAL
UNION ALL
SELECT 'hh', 'hour', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'hh') FROM DUAL
UNION ALL
SELECT 'mi', 'minute', TRUNC(TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss'), 'mi') FROM DUAL;
Will yield these results:
'C 'CENTURY' TRUNC(TO_DATE('2020
-- ------------------ -------------------
cc century 2001-01-01 00:00:00
yy year 2020-01-01 00:00:00
q quarter 2020-04-01 00:00:00
mm month 2020-04-01 00:00:00
iw ISO week 2020-04-06 00:00:00
ww NLS_TERRITORY week 2020-04-08 00:00:00
dd day 2020-04-08 00:00:00
hh hour 2020-04-08 14:00:00
mi minute 2020-04-08 14:11:00
Note the date value always has year, month, day, hour, minute, and second. Even if the value is 0. I’ve worked with many developers who assumed TRUNC removed the lower resolution elements from the date value rather then zeroing them and were then surprised when their data didn’t shrink.
For the complete list of masks supported, see the SQL Reference.
TRUNC is often used to make varied values uniform. For example, if you have a bunch of values spread across a day you want to aggregate, you can do so by truncating the their values.
SQL> SELECT d, TRUNC(d)
2 FROM (SELECT TO_DATE('2020-04-08 03:12:34', 'yyyy-mm-dd hh24:mi:ss') d FROM DUAL
3 UNION ALL
4 SELECT TO_DATE('2020-04-08 14:11:34', 'yyyy-mm-dd hh24:mi:ss') d FROM DUAL
5 UNION ALL
6 SELECT TO_DATE('2020-04-08 22:57:19', 'yyyy-mm-dd hh24:mi:ss') d FROM DUAL);
D TRUNC(D)
------------------- -------------------
2020-04-08 03:12:34 2020-04-08 00:00:00
2020-04-08 14:11:34 2020-04-08 00:00:00
2020-04-08 22:57:19 2020-04-08 00:00:00
In a real example, I might want to look at the number of objects created in my schema by day. If I aggregate by the CREATED column I’ll get counts for each second any object was created. It’s a start but not quite what I was looking for.
SQL> SELECT created, COUNT(*) 2 FROM user_objects 3 GROUP BY created; CREATED COUNT(*) ------------------- -------- 2019-11-13 19:39:21 7 2019-11-13 19:39:46 5 2019-11-13 19:39:50 7 2019-11-13 19:39:56 11 2019-11-13 19:39:57 9 2019-11-13 19:40:52 17 . . . 2019-11-13 19:40:56 19 2019-11-13 19:41:14 7 2019-11-13 19:41:23 1 2019-11-13 19:41:26 5 2019-11-13 19:41:45 6 2020-01-20 18:08:42 1 2020-03-30 22:46:36 1
What I really want is to count within a day so using TRUNC(created) I can all of those individual times on 2019-11-13 as a single point in time (00:00:00) and aggregate by that.
SQL> SELECT TRUNC(created), COUNT(*) 2 FROM user_objects 3 GROUP BY TRUNC(created); TRUNC(CREATED) COUNT(*) ------------------- -------- 2020-01-20 00:00:00 2 2020-04-09 00:00:00 1 2020-12-21 00:00:00 1 2020-09-22 00:00:00 1 2020-10-24 00:00:00 12 2019-11-20 00:00:00 3 2020-01-19 00:00:00 2 2020-03-24 00:00:00 1 2021-01-21 00:00:00 1 2019-11-13 00:00:00 1046
It would also work to format the CREATED column as text and leave out the hours, minutes, and seconds. However, I will usually do my date aggregations and other manipulations on the date values and save the text formatting for the last step. Doing so ensures I have a usable value for sorting or date/time math or other date manipulations. For example, in the above query, if I want to display the data as “Nov 13, 2019” instead of “2019-11-13 00:00:00” I would not be able to sort it reliably if the values were returned as text with leading month name.
SELECT TO_CHAR(d, 'Mon DD, yyyy'), COUNT(*)
FROM (SELECT TRUNC(created) d FROM user_objects)
GROUP BY d
ORDER BY d;
The example above is perhaps a little too simple as the TO_CHAR and TRUNC could have all been combined in a single scope layer without the inline view; but the idea holds through more complex queries with multiple layers of views and table expressions.
The other, probably more common, use of TRUNC is as a base to construct other dates. In fact, the first query in this article does just that: “trunc(sysdate)” in that query is the beginning of today and “trunc(sysdate)-1” is the beginning of yesterday.
That’s fairly straight forward but TRUNC can be used to help create variable but consistent values. Some of them are inherent to the native masks. For example: TRUNC(dateval,’d’) is the beginning of the week for each value of dateval. For me, with NLS_TERRITORY=America that will return 00:00:00 of the latest Sunday prior to a given date value, which could be the same day.
SQL> select sysdate, trunc(sysdate,'d') from dual; SYSDATE TRUNC(SYSDATE,'D') ------------------- ------------------- 2021-01-21 21:42:05 2021-01-17 00:00:00
You can also use date math to construct relative values for a particular time. Such as 3pm of the given day. That can be calculated by adding 15 hours to 00:00:00 of the day which is derived by using TRUNC. It will work with numeric or interval math.
SQL> SELECT SYSDATE, 2 TRUNC(SYSDATE) + 15 / 24 numeric_math, 3 TRUNC(SYSDATE) + INTERVAL '15' HOUR interval_math 4 FROM DUAL; SYSDATE NUMERIC_MATH INTERVAL_MATH ------------------- ------------------- ------------------- 2021-01-21 22:18:01 2021-01-21 15:00:00 2021-01-21 15:00:00
Using the connect by trick for generating numbers you can extend that to generate dates and times…
All hours in a day:
SELECT TRUNC(SYSDATE) + (LEVEL - 1) / 24 FROM DUAL CONNECT BY LEVEL <= 24
All months in a year:
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1) m FROM DUAL CONNECT BY LEVEL <= 12
All days in a month:
SELECT TRUNC(SYSDATE, 'mm') + LEVEL - 1 d
FROM DUAL
CONNECT BY TRUNC(SYSDATE, 'mm') + LEVEL - 1 < ADD_MONTHS(TRUNC(SYSDATE, 'mm'), 1)
Every 15 minutes from 8am to 5pm for a day:
SELECT TRUNC(SYSDATE) + INTERVAL '8' HOUR + NUMTODSINTERVAL((LEVEL - 1) * 15, 'minute') FROM DUAL
CONNECT BY TRUNC(SYSDATE) + INTERVAL '8' HOUR + NUMTODSINTERVAL((LEVEL - 1) * 15, 'minute') <=
TRUNC(SYSDATE) + INTERVAL '17' HOUR;
TRUNC and Timestamps
One of the most notable features of the TRUNC function is that it will always return a DATE type, regardless of the input parameter’s type. This can be convenient if you have mixed type inputs and you only want DATE type outputs. However, sometimes you want to preserve the data type.
If the input is a TIMESTAMP then returning the same is easily achieved with a simple cast after using TRUNC to the corresponding mask as a DATE.
SQL> WITH data 2 AS(SELECT TIMESTAMP '2020-11-20 12:34:56.789012345' ts FROM DUAL) 3 SELECT 'day' mask_type, CAST(TRUNC(ts) AS TIMESTAMP(9)) trunc_result FROM data 4 UNION ALL 5 SELECT 'hour', CAST(TRUNC(ts, 'hh') AS TIMESTAMP(9)) FROM data 6 UNION ALL 7 SELECT 'quarter', CAST(TRUNC(ts, 'q') AS TIMESTAMP(9)) FROM data 8 UNION ALL 9 SELECT 'minute', CAST(TRUNC(ts, 'mi') AS TIMESTAMP(9)) FROM data 10 UNION ALL 11 SELECT 'year', CAST(TRUNC(ts, 'yyyy') AS TIMESTAMP(9)) FROM data; MASK_TY TRUNC_RESULT ------- ----------------------------- day 2020-11-20 00:00:00.000000000 hour 2020-11-20 12:00:00.000000000 quarter 2020-10-01 00:00:00.000000000 minute 2020-11-20 12:34:00.000000000 year 2020-01-01 00:00:00.000000000
If you want to TRUNC to the second, there are a few ways to do that. A couple of the simplest are using Date addition of 0 which will implicitly convert TIMESTAMP to a DATE, dropping the fractional seconds and then add 0 which, obviously, doesn’t change the value. Then CAST that result back to a TIMESTAMP. Alternately, you can explicitly CAST the TIMESTAMP to a DATE and then CAST it back to a TIMESTAMP.
SQL> WITH data 2 AS(SELECT TIMESTAMP '2020-11-20 12:34:56.789012345' ts FROM DUAL) 3 SELECT 'date_math' trunc_type, CAST((ts + 0) AS TIMESTAMP(9)) trunc_result FROM data 4 UNION ALL 5 SELECT 'date_cast', CAST(CAST(ts AS DATE) AS TIMESTAMP(9)) FROM data; TRUNC_TYP TRUNC_RESULT --------- ----------------------------- date_math 2020-11-20 12:34:56.000000000 date_cast 2020-11-20 12:34:56.000000000
It might be tempting to try casting the timestamp to TIMESTAMP(0) but that will round the result rather than truncate it as seen below.
SQL> WITH data 2 AS(SELECT TIMESTAMP '2020-11-20 12:34:56.789012345' ts FROM DUAL) 3 SELECT CAST(ts AS TIMESTAMP(0)) 4 FROM data; CAST(TSASTIMESTAMP(0)) 2020-11-20 12:34:57.
Truncating a TIMESTAMP WITH TIME ZONE is a little trickier, because any intermediate DATE result will lose the time zone information. So, the same steps used above can be reused but the time zone will have to be reassigned from the original. The assignment can be done using the FROM_TZ function for the assignment and TO_CHAR with the TZR mask to extract the time zone name.
SQL> WITH data AS(SELECT TIMESTAMP '2020-11-20 12:34:56.789012345 US/Eastern' tstz FROM DUAL) 2 SELECT 'day' mask_type, FROM_TZ(CAST(TRUNC(tstz) AS TIMESTAMP(9)), TO_CHAR(tstz, 'TZR')) trunc_result FROM data 3 UNION ALL 4 SELECT 'hour', FROM_TZ(CAST(TRUNC(tstz, 'hh') AS TIMESTAMP(9)), TO_CHAR(tstz, 'TZR')) FROM data 5 UNION ALL 6 SELECT 'quarter', FROM_TZ(CAST(TRUNC(tstz, 'q') AS TIMESTAMP(9)), TO_CHAR(tstz, 'TZR')) FROM data 7 UNION ALL 8 SELECT 'minute', FROM_TZ(CAST(TRUNC(tstz, 'mi') AS TIMESTAMP(9)), TO_CHAR(tstz, 'TZR')) FROM data 9 UNION ALL 10 SELECT 'year', FROM_TZ(CAST(TRUNC(tstz, 'yyyy') AS TIMESTAMP(9)), TO_CHAR(tstz, 'TZR')) FROM data; MASK_TY TRUNC_RESULT ------- ---------------------------------------- day 2020-11-20 00:00:00.000000000 US/EASTERN hour 2020-11-20 12:00:00.000000000 US/EASTERN quarter 2020-10-01 00:00:00.000000000 US/EASTERN minute 2020-11-20 12:34:00.000000000 US/EASTERN year 2020-01-01 00:00:00.000000000 US/EASTERN
I hope readers found this useful, I wrote it and chose examples based on common questions I’ve received and requirements I’ve had to implement. Questions and comments, as always, are welcome.
TRUNC is awesome 🙂