extract does return a number, but if you concatenate a number to a - the final resulting datatype has to be a string type.

Yes, but if you to_char a number with a 'yy' format mask, you'll get an error: (-1481).

SQL> SELECT to_char(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE,-3)),'yy') dt FROM DUAL;
SELECT to_char(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE,-3)),'yy') dt FROM DUAL
ORA-01481: invalid number format model

pablolee wrote on Wed, 25 June 2014 17:03

Yes, but if you to_char a number with a 'yy' format mask, you'll get an error: (-1481).

Perhaps you mean to_char on what extract returns, and it certainly throws invalid number error.

SQL> SELECT to_char(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE,-3)),'yy') dt FROM DUAL;
SELECT to_char(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE,-3)),'yy') dt FROM DUAL
ORA-01481: invalid number format model

The workaround would be to convert it into date using to_date. But that would be just too much of unnecessary work.

Michel's query does the job perfectly.

But I don't understand what you mean about the error with 'yy' format mask. I think it would throw same error for 'yyyy' too.

So use to_char with a format mask of YY.

We're probably talking at cross purposes. Michel demonstrated exactly what I was talking about above. Which is ditch extract and just use to_char. I assume you were thinking I meant to use to_char on the result of extract, rather than instead of it.

But i want output like:-
14-15

SELECT  MOD(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE,-3)),100)
        || '-'
        || MOD(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, 9)),100) str
  FROM  DUAL
/

STR
------
14-15

SQL>