CODEWITHSUNDEEP
pythonoracle-databasestored-procedures
vikrant rana
vikrant rana

Reputation: 4679

ORA-01861 date column issue in stored procedure

I have a below query in oracle stored procedure which is causing the issue.

Select * 
from my_table 
where TO_CHAR(TO_DATE(billing_date,'DD-MON-YY',NLS_DATE_LANGUAGE=English'),'YYYY-MM-DD') ='2018-06-1';

I am getting ORA-01861:literal does not match format string.

My billing_date is having date like 01-JUN-18 and it's a date type column

I am executing this stored procedure using cx_Oracle in Python.

Upvotes: 0

Views: 144

Answers (2)

Littlefoot
Littlefoot

Reputation: 143013

Reading your code, it appears that billing_date column's datatype is varchar2. If so, this might help:

SQL> with my_table (id, billing_date) as
  2    (select 1, '20-jan-20' from dual union all
  3     select 2, '16-jun-19' from dual union all
  4     select 3, '01-jun-18' from dual
  5    )
  6  select *
  7  from my_table
  8  where billing_date = to_char(date '2018-06-01', 'dd-mon-yy', 'nls_date_language = english');

        ID BILLING_D
---------- ---------
         3 01-jun-18

SQL>

If, on the other hand, billing_date is date, then it gets simpler:

SQL> with my_table (id, billing_date) as
  2    (select 1, date '2020-01-20' from dual union all
  3     select 2, date '2019-06-16' from dual union all
  4     select 3, date '2018-06-01' from dual
  5    )
  6  select *
  7  from my_table
  8  where billing_date = date '2018-06-01';

        ID BILLING_
---------- --------
         3 01.06.18

SQL>

Upvotes: 2

Popeye
Popeye

Reputation: 35920

Simply use dates as following:

Select * from my_table where billing_date = date'2018-06-01';

Considering that your billing_date is of date type column.

Cheers!!

Upvotes: 2

Related Questions