Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Quarters between dates?
llevity wrote:
> Is there a way to determine how many quarters have elapsed between two
> dates? I'm trying to come up with a clever way of doing it, rather
> than loops and if statements.
>
> An example is I have a start date of December 31st, 1999. An end date
> of January 2nd 2000. This date range spans 2 quarters. If the start
> date was Dec 1st, it would only span one quarter.
i assume you mean Jan 1st, not Dec 1st...
Dec 31 - Jan 2:
SQL> select
2 months_between(
3 trunc(to_date('02-jan-00'),'Q'), 4 trunc(to_date('31-dec-99'),'Q')5 )/3 + 1 SPAN_QUARTERS from dual;
SPAN_QUARTERS
2
Jan 1 - Jan 2:
SQL> run
1 select
2 months_between(
3 trunc(to_date('02-jan-00'),'Q'), 4 trunc(to_date('01-jan-00'),'Q')5* )/3 + 1 SPAN_QUARTERS from dual
SPAN_QUARTERS
1
Jan 1 - Sep 29:
SQL> run
1 select
2 months_between(
3 trunc(to_date('29-sep-00'),'Q'), 4 trunc(to_date('01-jan-00'),'Q')5* )/3 + 1 SPAN_QUARTERS from dual
SPAN_QUARTERS
3 Received on Tue Jun 14 2005 - 10:49:23 CDT