Search code examples
sqloracle-databasecaseora-01722

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

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.

Solution

  • 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.