CODEWITHSUNDEEP
oracle-databaseplsql
Kay
Kay

Reputation: 915

cannot get year-month interval

I am learning PL-SQL and the exercise is to find the year-month interval difference between two dates. I wrote the following code:

DECLARE
  t1 TIMESTAMP (2) WITH TIME ZONE :=  to_timestamp_tz('2019-01-21 21:05:53.46 +02:00',
                        'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');
  t3 TIMESTAMP WITH TIME ZONE := to_timestamp_tz('2020-01-21 21:05:53.46 +02:00',
                        'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');                       
  ym INTERVAL YEAR(2) to MONTH;   
BEGIN
--  ym := '10-2';
  ym := t3-t1;
  DBMS_OUTPUT.PUT_LINE(ym);
END;

I would expect the ym variable to give '01-0' (1 year difference), but I get an error:

Error report -
ORA-06550: line 9, column 9:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I am really confused why this is happening, I tried changing the precision of the YEAR(_), but that doesn't help.

If ym is of datatype INTERVAL DAY(2) to SECOND(2), I get correct result. If I replace ym to ym := '10-2'; it also works. But with ym INTERVAL YEAR(2) to MONTH it is not working :(

Upvotes: 1

Views: 97

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

I found in the psoug.org examples that you could use this syntax:

ym := (t3-t1) year to month;

dbfiddle demo

Upvotes: 2

MT0
MT0

Reputation: 167822

t3-t1 does not work as the resulting value is of the INTERVAL DAY TO SECOND data type and there is no implicit cast to INTERVAL YEAR TO MONTH.

Instead, you can use NUMTOYMINTERVAL( MONTHS_BETWEEN( t3, t1 ), 'MONTH' ); to dynamically create the correct data type:

DECLARE
  t1 TIMESTAMP (2) WITH TIME ZONE :=  to_timestamp_tz('2019-01-21 21:05:53.46 +02:00',
                        'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');
  t3 TIMESTAMP WITH TIME ZONE := to_timestamp_tz('2020-01-21 21:05:53.46 +02:00',
                        'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');                       
  ym INTERVAL YEAR(2) to MONTH;   
BEGIN
  ym := NUMTOYMINTERVAL( MONTHS_BETWEEN( t3, t1 ), 'MONTH' );
  DBMS_OUTPUT.PUT_LINE(ym);
END;
/

outputs:

+01-00

db<>fiddle here

Upvotes: 2

Related Questions