0 
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:
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
Related posts:
Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings 
Anti-Search patterns – SQL to look for what is NOT there – Part Two AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december Juggling with SQL Types in SQL and PL/SQL – fading borders between Collection and Table Analyzing Match Scores in ‘fuzzy searches’ – explaining the shades of gray in a non-binary world
how can I obtain year from period ‘201401’ in plsql…Thanks