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.
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
- Aggregating in SQL across multiple levels and along several dimensions using ROLLUP and GROUPING SETS
- Anti-Search patterns – SQL to look for what is NOT there – Part Two
- How to calculate the Easter date in PL/SQL
- Summary results for all dates, including the ones that I do not have data for – example of using Partition Outer Join – Oracle10g SQL Feature
- Analyzing Match Scores in 'fuzzy searches' – explaining the shades of gray in a non-binary world
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Oracle SQL: Using subquery factoring in an INSERT statement
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation
- AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december
- The APEX of Business Value… or: the Business Value of APEX? Cloud takes Oracle APEX to new heights!