At the beginning of every month, my Ops executive colleague always send me email, asking for a report on jobs carried out last month. I would then open up my Oracle Developer window, run query, export the result to Excel, then mail it back to him.
The query look something like this:
select *
from TBL_Job
where CreateDate >= to_date('01 Feb 2015', 'DD MON YYYY')
and CreateDate < to_date('01 Mar 2015', 'DD MON YYYY')
I've been thinking of moving this report to our Jasper Server. This way I can schedule the report to run, then automatically mail the result in Excel format. In order to do that, I need to change the query. And that's where the TRUNC function comes to shine.
Trunc (short for truncate), will strip any extra value from a DateTime type. You can think of it as Floor (round-down) function for a DateTime type.
E.g. If you just apply Trunc to a DateTime, it will strip away the Time value.
select Trunc(sysdate) from dual; --Output: 13-MAR-2015 00:00:00
You can also specify how far the truncation will go
select Trunc(sysdate, 'MONTH') from dual; --Output: 01-MAR-2015 00:00:00
Other useful examples of Trunc:
-- First day of this year
select trunc(sysdate, 'YEAR') from dual;
-- Output: 01-JAN-2015 00:00:00
-- First day of this quarter
select trunc(sysdate, 'Q') from dual;
-- Output: 01-JAN-2015 00:00:00
-- First day in the quarter of 18 Aug 2015
select trunc(to_date('18 AUG 2015','DD MON YYYY'), 'Q') from dual;
-- Output: 01-JUL-2015 00:00:00
-- First day of this week
select trunc(sysdate, 'W') from dual;
-- Output: 08-MAR-2015 00:00:00
With this information, we can replace the original query
select * from TBL_Job where CreateDate >= trunc(trunc(sysdate, 'MONTH')-1, 'MONTH') and CreateDate < trunc(sysdate, 'MONTH')
This way, at any time we run the query, it will always return last month data.
I hope it helps,
Cheers
About Hardono
and 
