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
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
- Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL
- Oracle Database 12c: joining and outer joining with collections
- Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack
- Oracle Database 12c: In Line PL/SQL Functions in SQL queries