Using TRUNC in SQL to get the first date in a period

0

While working on a Web application that required some page to report data aggregated on several levels of time period – by month, quarter or year – I ran into a very handy way to use the TRUNC function. I was not aware of this usage until now – and since I make it a point to encourage my colleagues to write a blog article on anything new they uncover – no matter how trivial it may seem or how strong the feeling that surely everyone already knows about that – I must play by my own rules and tell you about it.

The challenge I was facing can easily be translated into this question: write a SQL query that reports on the number of employees hired per month (or quarter or year). In fact, use a bind parameter that drives the aggregation level – i.e. the Month, Quarter or Year.

Taking the well known EMP table as the target system, this challenge means that all employees hired in the same Month (or Quarter or Year) should be aggregated in the same group. That in turn means that the aggregation should take place by the MONTH (or QUARTER or YEAR) component of their HIREDATE. Instead of fiddling with TO_CHAR using different date identifiers (which also have been an option) it turns out to be even easier to aggregate by the desired truncated portion of the date value in hiredate.

The following query does the trick:

image

with TRUNC(HIREDATE, ‘YY’) producing the first date in the year in which the HIREDATE falls. The result of this query:

image

By changing the zoom level to Q and MM respectively, the same query returns the results aggregated at the Quarter and Month level.

image

image

I could easily leverage this query in my application using a real bind parameter supplied by the application instead of the inline view binds that was used here.

 

Resources

Oracle Documentation on the Format identifiers available with TRUNC for Dates: http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions255.htm#i1002084

Blog article on playing with TRUNC and Dates in Oracle Database: http://nimishgarg.blogspot.in/2011/03/playing-with-truncate-and-date.html

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Leave a Reply