Quarters tip and / or question 2

[missy8]

I’ve been working on reports and needed to get some quarters for a parameter list.
This may be of interest to anyone who runs quarterly reports.
However I also have a question and that is, am I making this complicated, is there a simpler way to do this?

Basically to get the current quarter for the report parameter you need to use
=left(((Month(Now) - 1) / 3),1) + 1

In Oracle its (trunc(((to_number(to_char(sysdate,'mm')) - 1)/3) + 1)) current_quarter

But to get the previous quarter and make sure you get the Q4 for previous year where required you need
to do the following.
In other words since we are in Q1 of 2007 the previous Q will be Q4

select decode(trunc(((to_number(to_char(sysdate,'mm')) - 1)/3) + 1), 1, 4,trunc(((to_number(to_char(sysdate,'mm')) - 1)/3) + 1)) previous_quarter
from dual


THANKS
Missy

 

[jimirvine]

try

select to_char(sysdate,'Q') from dual;

HTH
Jim

 

[missy8]

Thanks nothing like making 4 times the amount of work for yourself because you don’t know what Oracle can do with dates – SILLY ME

 

[Welshbird]

Missy - just a little nudge to say maybe you'd like to awward jimiirvine with a wee purple star.....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]