concatenate with leading zeros for months – not a valid month error

I want to concatenate string with year in oracle. The string is month. Query is

 select to_date('11'||EXTRACT(YEAR FROM SYSDATE)-1,'MMYYYY') from dual;

This query wrks fine however if the string is ’01’ or anything starting with zero i am getting not a valid month error.

select to_date('04'||EXTRACT(YEAR FROM SYSDATE)-1,'MMYYYY') from dual;
select to_date('05'||EXTRACT(YEAR FROM SYSDATE)-1,'MMYYYY') from dual;

Answer

Let’s run it without the to_date(..., ...) wrapper:

select '04'||EXTRACT(YEAR FROM SYSDATE)-1 from dual;

'04'||EXTRACT(YEARFROMSYSDATE)-1
--------------------------------
                           42019

Do you see the problem? (Note that the result is a number, not even a string.)

Concatenation is performed before you subtract 1. You get the string ‘042020’ first, and then you subtract 1; the string is converted to number, and you get the result 42019. When you apply TO_DATE() this is converted back to string, and the first thing Oracle will choke on is the 42 for month.

Use parentheses around the arithmetic operation to force the subtraction to be performed first, and it will work:

select to_date('04'||(EXTRACT(YEAR FROM SYSDATE)-1), 'MMYYYY') from dual;

TO_DATE('04'||(EXTR
-------------------
2019-04-01 00:00:00