"ORA-01861: literal does not match format string" Error in PL/SQL

Question

Can someone help me understand what the following code line is doing wrong?

res_start_time_ := to_date(to_char(account_date_, 'YYYYMMDD ') || sched_ftime_, 'YYYYMMDD HH24:MI');

res_start_time_ and account_date_ are of DATE type.

sched_ftime_ is VARCHAR2 type and it can be NULL.

In a test scenario, I get the ORA-01861: literal does not match format string error when there is a value for account_date_ and NULL for sched_ftime_.

Can someone explain to me what I am doing wrong and how I can get rid of this error?

Answer 1

The source of the issue is you are allowing the time component (sched_ftime_) to assume values that you do not know the exact format before hand. You can do this, but it comes with a coding requirement. You need to establish the permissible and then derive the FORMAT SPECIFICATION at run time, and throw an error if the value does not match a permissible pattern. What is permissible and the validation is as simple or complicated as you want. As a demonstration the following function looks for 5 patterns:hh24mi, hh24miss, hh24:mi, hh24:mi:ss, and null. For those it returns the appropriate date, for anything else it returns a application defined exception.

create or replace 
function get_actual_date_time( account_date_  date
                             , sched_ftime_   varchar2
                             ) 
  return date
is 
  k_format_base          constant varchar2(8)  := 'yyyymmdd';
  k_bad_time_format_msg  constant varchar2(32) := ' invalid time specification.';  
  -- declare regexp for valid time formats 
  k_regx_time_hh24mm     constant varchar2(7)  := '^\d{4}$';
  k_regx_time_hh24mmss   constant varchar2(7)  := '^\d{6}$'; 
  k_regx_time_hh24mm_s   constant varchar2(11) := '^\d\d:\d\d$'; 
  k_regx_time_hh24mmss_s constant varchar2(16) := '^\d\d:\d\d:\d\d$';  
  
  -- declare actual format specification corresponding to valid format
  k_fmt_time_hh24mm      constant varchar2(6)  := 'hh24mi';
  k_fmt_time_hh24mmss    constant varchar2(8)  := 'hh24miss'; 
  k_fmt_time_hh24mm_s    constant varchar2(7)  := 'hh24:mi';
  k_fmt_time_hh24mmss_s  constant varchar2(10) := 'hh24:mi:ss';
  
  l_time_format varchar2(16); 
begin     
  case when sched_ftime_ is null then
            l_time_format := null;
       when regexp_like ( sched_ftime_,k_regx_time_hh24mm) then
            l_time_format := k_fmt_time_hh24mm; 
       when regexp_like ( sched_ftime_,k_regx_time_hh24mmss) then
            l_time_format := k_fmt_time_hh24mmss;  
       when regexp_like ( sched_ftime_,k_regx_time_hh24mm_s) then
            l_time_format := k_fmt_time_hh24mm_s;  
       when regexp_like ( sched_ftime_,k_regx_time_hh24mmss_s) then
            l_time_format := k_fmt_time_hh24mmss_s;  
       else 
           raise_application_error( -20109,'''' || sched_ftime_ || '''' || k_bad_time_format_msg); 
  end case; 
       
  return to_date(to_char(account_date_,k_format_base) || sched_ftime_ 
                , k_format_base || l_time_format);
end get_actual_date_time;

Keep in mind the above is an example only and there are many enhancement to be made. For example is will accept time specification of 99:99:99 even though it is obviously a invalid time specification. But it fits the validation for '^\d\d:\d\d:\d\d$'. Neither does it attempt to validate the valid time specification 06:45 PM. See fiddle here.

Answer 2

The source code is attempting to form a string that can be converted to a date having both a day value and a time-of-day value.

This to_char(account_date_, 'YYYYMMDD ') converts a date value into a 9 character string ending with a space which permits use of string concatenation of what should be value containing hours and minutes. Once concatenated it then attempts to convert that into a date value accurate to a minute.

However the error encountered will occur if a non-null value of sched_ftime_ isn't in a form that can be transformed to HH24:MI e.g. '123456' is to long to be interpreted as only hours and minutes.

This can be replicated:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
DECLARE
  res_start_time_ DATE;
  account_date_   DATE := TRUNC(SYSDATE);
  sched_ftime_    VARCHAR2(20) := '123456'; /* this value fails */
BEGIN
  res_start_time_ := to_date(
                       to_char(account_date_, 'YYYYMMDD ') || sched_ftime_,
                       'YYYYMMDD HH24:MI'
                     );
  DBMS_OUTPUT.PUT_LINE(res_start_time_);
END;
/

ORA-01861: literal does not match format string
ORA-06512: at line 6

However a shorter value e.g. '1234' can be interpreted as hours and minutes:

DECLARE
  res_start_time_ DATE;
  account_date_   DATE := TRUNC(SYSDATE);
  sched_ftime_    VARCHAR2(20) := '1234'; /* this value works */
BEGIN
  res_start_time_ := to_date(
                       to_char(account_date_, 'YYYYMMDD ') || sched_ftime_,
                       'YYYYMMDD HH24:MI'
                     );
  DBMS_OUTPUT.PUT_LINE(res_start_time_);
END;
/

1 rows affected

dbms_output:
2021-08-27 12:34:00

Hence I suggest you protect the conversion into a date by limiting the length of your second parameter e.g. to 4 characters perhaps using substr()

DECLARE
  res_start_time_ DATE;
  account_date_   DATE := TRUNC(SYSDATE);
  sched_ftime_    VARCHAR2(20) := '123456'; /* this value gets truncated */
BEGIN
  res_start_time_ := to_date(
                       to_char(account_date_, 'YYYYMMDD ') 
                               || substr(sched_ftime_,1,4),
                       'YYYYMMDD HH24:MI'
                     );
  DBMS_OUTPUT.PUT_LINE(res_start_time_);
END;
/

You may need other validations on that varchar2 value such that they are all digits as well. Or, if you are including the colon into the hour & minutes value then the overall length needs to be 5 chars. In short you need to vet the hours and minutes so that they are logical and valid.

nb: Kudos to MT0 for the source db<>fiddle which I extended here