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

Incoming Search

oracle, sql