Re: Quarters between dates?

From: JSchneider <jeremypaulschneider_at_gmail.com>
Date: 14 Jun 2005 08:49:23 -0700
Message-ID: <1118764163.042877.143320@g43g2000cwa.googlegroups.com>

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...

1. truncate the dates to the quarter
2. get months_between quarters (or just subtract them, whatever)

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