date format picture ends before converting entire input string error

Question

I have this procedure:

create or replace Procedure return_rows_LECTURE_BY_DATE (in_date in date, out_cursor OUT SYS_REFCURSOR) As

Begin
     OPEN out_cursor for
     select *
     FROM COURSE_LECTURE
     WHERE LECT_DATE_TIME_START >= to_timestamp(in_date, 'dd-mm-yyyy')
     and       LECT_DATE_TIME_START < to_timestamp(in_date+1, 'dd-mm-yyyy')
    ORDER BY LECT_DATE_TIME_START;
End;

input: date, output: lectures on this date. The dates in the table (view) is TIMESTAMP.

I want to run this procedure. I tried this:

declare 
k SYS_REFCURSOR;
--t DATE:= to_date('2010-12-14:09:56:53', 'YYYY-MM-DD:HH24:MI:SS') ;
res COURSE_LECTURE%rowtype; 
begin
return_rows_LECTURE_BY_DATE(to_date('2010-12-14', 'YYYY-MM-DD'),k);
loop
  FETCH  k into res;
  Exit when k%notFound;
  DBMS_OUTPUT.PUT_LINE(res.COURSE_NAME );
  end loop;
end;

But I got this error:

Error report - ORA-01830: date format picture ends before converting entire input string ORA-06512: at "HR.RETURN_ROWS_LECTURE_BY_DATE", line 4 ORA-06512: at line 6 01830. 00000 - "date format picture ends before converting entire input string"

paubo147 paubo147 · Accepted Answer · 2014-07-25T08:10:09

You are converting the date into a timestamp, by using TO_TIMESTAMP(), which takes a character as a parameter. You should use CAST() instead, which converts one datatype to another; for instance:

WHERE LECT_DATE_TIME_START >= CAST(in_date AS TIMESTAMP)

You should be doing this with all of your conversions from dates to timestamps; so to_timestamp(in_date+1, 'dd-mm-yyyy') becomes CAST((in_date + 1) AS TIMESTAMP).

date format picture ends before converting entire input string error

2 Answers