HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

Oracle Query by Quarter

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
oraclequeryquarter

Problem

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should:

select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ...
from ...
where eventdate between (sysdate - 458) and sysdate


It doesn't do what I want. How do I make sure I get all rows for the current quarter and the last 4, no matter what day of the quarter it is?

Solution

testbed:

create table foo as
select add_months(sysdate, -(level-1)*3) as eventdate from dual connect by level

query:

select to_char(eventdate,'YYYY "Q"- Q') -- "Q" is treated as a literal due to the quotes
from foo
where eventdate>=add_months(trunc(sysdate, 'Q'), -12); -- trunc() gets first day of the Q
-- then add_months takes us back 4 Qs
/*
TO_CHAR(EVENTDATE,'YYYY"Q"-Q')
------------------------------
2011 Q- 4
2011 Q- 3
2011 Q- 2
2011 Q- 1
2010 Q- 4
*/
`

Context

StackExchange Database Administrators Q#9951, answer score: 4

Revisions (0)

No revisions yet.