CODEWITHSUNDEEP
sqloracle-database
casper1111
casper1111

Reputation: 5

literal string does not match format string

I'm trying to run an sql script against a test database. I'm getting the error "literal string does not match format string" - weird thing is, a coworker of mine runs the same query on the same test server and is not getting any errors, only when I run it, I get the errors. I'm completely lost on the matter. Where should I go from here?

I'm running my query with SQLPlus command line. I'm receiving the error on the first line: select NVL.....

  select NVL(to_char(add_months(to_date(max(mem.birth_date)), + 780), 'yyyymmdd'), ' ')
    into local_turn_65_on
    from member mem
   where mem.member_id in (
         select cmr.member_id
         from case_member cmr
         where cmr.case_id = due_tasks_rec.case_id
           and cmr.current_row_ind = 'Y'
           and cmr.case_member_end_date = 29991231
           and cmr.case_member_role in ('AA','AB','AD','DP'))
     and mem.update_end_date = 29991231
     and mem.birth_date between
         (to_char(add_months(sysdate,-780), 'yyyymmdd'))
     and
         (to_char(add_months(sysdate,-780), 'yyyy') || '1231');

If someone could point me in the right direction, I'd greatly appreciate it!

Upvotes: 0

Views: 109

Answers (2)

Himanshu
Himanshu

Reputation: 3970

I guess an extra to_char must have resolved the problem just causing that number format be a character string and then using to_date and adding months would have worked.

select NVL(to_char(add_months(to_date(to_char(max(mem.birth_date))), + 780), 'yyyymmdd'), ' ')

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

data type is number(8)

Then you need to specify the format model for the conversion of that number to a date; instead of

to_date(max(mem.birth_date))

you need to do:

to_date(max(mem.birth_date), 'YYYYMMDD')

or even more explicitly:

to_date(to_char(max(mem.birth_date)), 'YYYYMMDD')

At the moment your code is relying on your current session's NLS settings to get the format model to use, and the error means it doesn't match the value you're passing. The reason your coworker is able to run the same query successfully is that their session has different NLS settings. This is why you shouldn't rely on NLS settings (or implicit conversions - they often go together) as you have no control over how other people will run your code.

You can see the effect with a cut-down example:

alter session set nls_date_format = 'DD-Mon-RR';

with mem (birth_date) as (select 20190328 from dual)
select NVL(to_char(add_months(to_date(max(mem.birth_date)), + 780), 'yyyymmdd'), ' ')
from mem;

ORA-01861: literal does not match format string

or even more simply:

with mem (birth_date) as (select 20190328 from dual)
select to_date(max(mem.birth_date))
from mem;

ORA-01861: literal does not match format string

Adding the format model produces a date:

with mem (birth_date) as (select 20190328 from dual)
select to_date(max(mem.birth_date), 'YYYYMMDD')
from mem;

TO_DATE(M
---------
28-Mar-19

and your adjustment and conversion works now too:

with mem (birth_date) as (select 20190328 from dual)
select NVL(to_char(add_months(to_date(max(mem.birth_date), 'YYYYMMDD'), + 780), 'yyyymmdd'), ' ')
from mem;

NVL(TO_C
--------
20840328

You should also store dates as dates, not as numbers, but that's a separate issue...

Upvotes: 1

Related Questions