Skip to Main Content
  • Questions
  • Getting ORA-01861 literal does not match format string error in stored procedure

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ankit.

Asked: October 12, 2017 - 5:17 am UTC

Last updated: October 16, 2017 - 2:42 pm UTC

Version: 12.1.0

Viewed 10K+ times! This question is

You Asked

Hi,

We have a requirement to get the date in 'yyyy-mm-dd' format but unable to do using below procedure,

create table emp_test (emp_id number, customer_due_date date)
/

create table dep_test (due_days number)
/

<Insert>,

insert into emp_test values (1,sysdate)
/
insert into emp_test values (2,sysdate-1)
/
insert into emp_test values (3,sysdate-2)
/
insert into emp_test values (4,sysdate+1)
/
insert into emp_test values (5,sysdate+2)
/
insert into dep_test values (2)
/

commit
/

<Stored Procedure>

Create or replace procedure test_proc (p_emp_id in emp_test.emp_id%type, p_due_date out date)
as
Begin
select ( case when trim(to_char(trunc(o.customer_due_date) - r.due_days, 'Day')) = 'Sunday'
              then trunc(o.customer_due_date) - r.due_days - 2 
              when trim(to_char(trunc(o.customer_due_date) - r.due_days, 'Day')) = 'Saturday'
              then trunc(o.customer_due_date) - r.due_days - 1
              when trunc(o.customer_due_date) is null 
              then trunc(sysdate) + 60
          else 
              trunc(o.customer_due_date) - r.due_days
         end) Due_date
into p_due_date
from 
   emp_test o,
   dep_test r
where
  o.emp_id = p_emp_id;

end;
/


I need to get p_due_date in 'yyyy-mm-dd' format but its giving p_due_date in 'dd-mon-yy' format.

Please suggest.



and Chris said...

Unless you to_char the date with a specific format mask, the database will use your NLS settings to do the conversion.

To stop this, to_char the data with your desired format:

select value from nls_session_parameters
where  parameter = 'NLS_DATE_FORMAT';

VALUE                   
DD-MON-YYYY hh24:mi:ss 

declare
  emp_id integer := 1;
  due_date date;
begin
  test_proc (p_emp_id => 1, p_due_date => due_date);
  dbms_output.put_line(due_date);
end;
/

10-OCT-2017 00:00:00

alter session set NLS_DATE_FORMAT = 'YY MON';

declare
  emp_id integer := 1;
  due_date date;
begin
  test_proc (p_emp_id => 1, p_due_date => due_date);
  dbms_output.put_line(due_date);
end;
/

17 OCT

declare
  emp_id integer := 1;
  due_date date;
begin
  test_proc (p_emp_id => 1, p_due_date => due_date);
  dbms_output.put_line(to_char(due_date, 'YYYY-MM-DD'));
end;
/

2017-10-10

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

followup

A reader, October 16, 2017 - 3:59 am UTC

thanks...is it possible without altering a session or changing data type of out parameter?

Please suggest.
Chris Saxon
October 16, 2017 - 12:33 pm UTC

No. You have to set the NLS parameters or (better) use to_char with a mask to set the format.

NextName, October 16, 2017 - 12:41 pm UTC

Wouldn't it be advisable to simply leave the procedure's output parameter a date -- and let the "consumer" (presentation layer/application/...) handle the formatting issure?

IOW, leave the date format as long as possible ...
Chris Saxon
October 16, 2017 - 2:42 pm UTC

Yes, it's a good idea to leave it as a date as possible. Particularly if you need to do arithmetic or similar processing on the value.

A reader, October 16, 2017 - 12:54 pm UTC

Thanks Chris!!!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.