ORA-01861: literal does not match format string

For any Concurrent Program date parameter in Oracle Apps, we assign FND_STANDARD_DATE Value Set having length 11.

This value set always pass date in YYYY/MM/DD  format  to our program

If the date format of your program (Report or PL/SQL Procedure) does not matches with above format, it will always throw below error in log file

ORA-01861: literal does not match format string

Below are the steps to resolve this error with different Program Type:

By

Oracle Reports Type Program

Define user parameter for Date in RDF with below property details

Datatype- Date
Width- 20
Input Mask- RRRR/MM/DD HH24:MI:SS


PL/SQL Stored Procedure Type Program

1) Always use errbuf and retcode as first two OUT parameters in your procedure

2) Define the Date parameters with datatype VARCHAR2 in procedure

3) Use fnd_date.canonical_to_date to convert varchar2 format to oracle date format (DD-MON-YY) and then use it anywhere in a program

Or Alternate of using  fnd_date.canonical_to_date is passing the variable with TRUNC(TO_DATE(p_start_date,’YYYY/MM/DD H24:MI:SS’))

Below is an example for the same

CREATE OR REPLACE PROCEDURE APPS.TEST_TRANSFER
( p_errbuf OUT VARCHAR2
,p_retcode OUT VARCHAR2
,p_from_date VARCHAR2
,p_to_date VARCHAR2 ) 
IS
v_from_date DATE;
v_to_date DATE;
BEGIN
v_from_date := fnd_date.canonical_to_date (p_from_date);
v_to_date := fnd_date.canonical_to_date (p_to_date);

OR By Using:

lv_start_date:= TRUNC(TO_DATE(p_start_date,'YYYY/MM/DD H24:MI:SS'))