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

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:

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

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

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