Issue using to_date giving ORA-01722: invalid number

I am trying to pull together two fields to create a mm/dd/yyyy date field using case-when, and it keeps giving me error ORA-01722: invalid number.

case when 
  extract(month from t.date) >= t.month 
  then to_date(t.month || '/' || '01' || '/' || extract(year from t.date), 'mm/dd/yyyy')
  else to_date(t.month || '/' || '01' || '/' || extract(year from t.date)-1, 'mm/dd/yyyy')
  end as effective_date

Answer

The problem is Oracle applies the concatenation operators before it does the arithmetic. So the ELSE branch of your CASE statement:

to_date(t.month || '/' || '01' || '/' || extract(year from t.date)-1, 'mm/dd/yyyy')

is going to execute something like '02/01/2019' - 1. Trying to subtract one from a string (crucially at this point it’s a string not a date) not unreasonably results in an ORA-01722.

What you need to do is wrap the extract year in something to force the minus operation before concatenation. This will do it:

to_date(t.month || '/' || '01' || '/' || to_char(extract(year from t.date)-1), 'mm/dd/yyyy')