adding years getting invalid number error in case statement only - ORA-01722

Question

Forgive me as I am new to this. I am trying to add a year to a date in a query. The year to be added is based on the month/day in the database. If the date is prior to November 1st then the year will be 2017 if it is after November 1st then it will be 2018. I have tried this a few ways (see below) and can get the years added in a query but when I put them in a case statement I get the 'Invalid Number' error.

Using to_char on the date:

   CASE
      WHEN to_char(au.creat_ts, 'MMDD') >= to_char('11/01/2018', 'MMDD') THEN
       to_char(to_date( '2017'||to_char(au.creat_ts,'MMDDHH24MISS'),
'YYYYMMDDHH24MISS' ), 'MM/DD/YYYY') 
      ELSE
      to_char(to_date( '2018'||to_char(au.creat_ts,'MMDDHH24MISS'), 'YYYYMMDDHH24MISS' ), 'MM/DD/YYYY') 
    END cmpltn_dt,

Adding Months:

  CASE
      WHEN to_char(au.creat_ts, 'MMDD') >= to_char('11/01/2018', 'MMDD') THEN
       trunc(add_months(au.creat_ts,
                        floor(months_between(SYSDATE, au.creat_ts) / 12) * 12)) --calcx --add years
      ELSE
       trunc(add_months(au.creat_ts,
                        (floor(months_between(SYSDATE, au.creat_ts) / 12) - 1) * 12))
    END calcx,

These run in select from dual statements with no error. Any ideas? Thanks so much in advance.

Answer 1

If AU.CREAT_TS is DATE, this works OK (meaning: it doesn't fail):

SQL> with au (creat_ts) as
  2    (select date '2018-12-05' from dual
  3    )
  4  select
  5    CASE WHEN to_char(au.creat_ts, 'MMDD') >= to_char(to_date('11/01/2018', 'dd/mm/yyyy'), 'MMDD') THEN
  6              trunc(add_months(au.creat_ts, floor(months_between(SYSDATE, au.creat_ts) / 12) * 12))
  7         ELSE trunc(add_months(au.creat_ts, (floor(months_between(SYSDATE, au.creat_ts) / 12) - 1) * 12))
  8    END calcx
  9  from au
 10  ;

CALCX
-------------------
05.12.2018 00:00:00

SQL>

Note the difference: you used

  CASE WHEN to_char(au.creat_ts, 'MMDD') >= to_char('11/01/2018', 'MMDD') THEN 

and it raises the error because '11/01/2018' is a string; it is not a date. If you want to use a date, you have to tell Oracle so. How? See my working example.

Answer 2

The errors are coming from the two appearances of this clause:

to_char('11/01/2018', 'MMDD')

You're passing a string as the first argument (not a date), and Oracle defaults to trying to convert that string to a number - which throws the error you see.

You either need to pass in an actual date, by explicitly converting the string:

to_char(to_date('11/01/2018', 'MM/DD/YYYY', 'MMDD')

or with an ANSI literal:

to_char(date '2018-11-01', 'MMDD')

or if it's really a fixed value don't convert it at all, just do:

'1101'

or if you are being passed that streing from elsewhere - and you're sure about the format, of course - us substr() to extract the bits you need without trying to bounce it through a date.

Incidentally, converting values to and from dates and concatenating bits of strings together looks a bit messy and error-prone. And if that fixed date is always the first of a month, you could modify your case expression to only look at the month, e.g. via extract().