CODEWITHSUNDEEP
sqloracle-database
Potato
Potato

Reputation: 172

How to take difference between two dates specified as year with quarter

I can't find the result of difference between two dates, my fortmat is 'YYYY-Q'

select MAX(t.period), MIN(t.period) from table t

and i want to put also MAX(t.period)-MIN(t.period) column. When i try to convert it from date to char i have problems (ORA-01722 uncorrect number)

select MAX(t.period), MIN(t.period) to_char(MAX(t.period), 'YYYY-Q') from table t

when i try to put to_date, i have problems also (ORA-01820: format code cannot appear in date input format)

select MAX(t.period), MIN(t.period) to_date(MAX(t.period), 'YYYY-Q') from table t

I just want to make difference between two dates, and cant make single converions... My period column format is the same as format i've put 'YYYY-Q', and if i have for example 2005-4 date - 2004-2 date i want to get 6 result, is it even possible?

Upvotes: 0

Views: 935

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do the arithmetic in the space of "quarters" rather than dates (or months).

So, convert a period to a number of quarters. For example, 2000-1 can be converted to 2000 * 4 + 1 = 80001. The difference of these numbers is what you want.

So:

select ( ( substr(max(period), 1, 4) * 4 + substr(max(period), -1) ) - 
         ( substr(min(period), 1, 4) * 4 + substr(min(period), -1) )
       ) as diff_q

Note that this uses implicit conversion, which should be fine if all your values really do follow this format.

Upvotes: 1

MT0
MT0

Reputation: 168741

You can convert the years component of your period to the start of a year using:

TO_DATE( SUBSTR( period, 1, 4 ) || '0101', 'YYYYMMDD' )

Then you can add 3 months for each quarter beyond the first using:

ADD_MONTHS(
  TO_DATE( SUBSTR( period, 1, 4 ) || '0101', 'YYYYMMDD' ),
  3 * ( SUBSTR( period, 6, 1 ) - 1 )
)

Then you can use MONTHS_BETWEEN to find the difference between the dates and divide by 3 to get the number of quarters difference.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( period ) AS
SELECT '2005-4' FROM DUAL UNION ALL
SELECT '2004-2' FROM DUAL;

Query 1:

SELECT MIN( period ) || ' - ' || MAX( period ) AS range,
       MONTHS_BETWEEN( MAX( period_start ), MIN( period_start ) ) / 3
         AS quarters_difference
FROM   (
  SELECT period,
         ADD_MONTHS(
           TO_DATE( SUBSTR( period, 1, 4 ) || '0101', 'YYYYMMDD' ),
           3 * ( SUBSTR( period, 6, 1 ) - 1 )
         ) period_start
  FROM   table_name
)

Results:

|           RANGE | QUARTERS_DIFFERENCE |
|-----------------|---------------------|
| 2004-2 - 2005-4 |                   6 |

Upvotes: 1

Related Questions