Tom, thank you for your always great help.

Here is some more clarification, if I may.

I have date1 generated by an other application (and passed to the stored procedure) and it looks like: "10-May-2005 2:14:33 PM"

I would like to enter this value with an Insert or with an Update statement, e.g.:

date1 = '10-May-2005 2:14:33 PM';

UPDATE t1
SET
field1 = TO_DATE(date1, 'dd-MM-yyyy HH:MI:SS'),...
....

This gives the error:
OCIStmtExecute: ORA-01830: date format picture ends before converting entire input string ORA-06512.


If I have just date, it is fine:

date1 = '10-May-2005';

UPDATE t1
SET
filed1 = TO_DATE(date1, 'dd-MM-yyyy'),
....

but in the database record I have this value:
"10-May-2005 12:00:00 AM"




If I have:

date1 = '10-May-2005 2:14:33 PM';

UPDATE t1
SET
field1 = date1,
....

I get the error again:
ociexecute(): OCIStmtExecute: ORA-01830: date format picture ends before converting entire input string ORA-06512


Field1 datatype is DATE.
How can I enter in the stored procedure the proper time, too?

Again, thank you very much for your help.

Nick

Nick,

in the example you gave
(assuming date1 is a varchar2 field)
you are getting the date format picture error because you
the month is displayed coming in as 'May' but you are using the MM format (which is expecting 05). You should be using the MON format.

so you should be using to_date("10-May-2005 2:14:33 PM",'DD-MON-YYYY HH:MM:SS PM);

You should also ensure that you use AM format when the string comes in indicating AM and PM when the string comes in indicating PM, otherwise your times could be out by 12 hrs

Thank you Tom and Keith for the great advice.

You were both right.

The fundamental problem was that I defined date1 as DATE, instead of varchar2.

Regarding passing on "May" or "05" Oracle at least gives a "black and white" error message.

Which makes me wonder: wouldn't be great if instead of displaying an error, Oracle (or other software, for that matter) would make an "educated guess" based on the input data, syntax error and error message - trying to "fix" the failed query, maybe offering possible working "is-that-what-you-really-wanted" versions if there is enough input+error information for constructing "meaningful guesses"?

Okay, I know I could also wish for software that could just read my mind, without all the steps in between, where most of the errors happen.

In the meantime, thank you Tom and Keith.

Nick

I was searching the net to find the exact syntax of query to insert 'time' along with date. After a long search, I came across this query. Though the query is not related to my question, it indirectly helped me a lot.

Thanks

I am passing 2 parameters to a oracle stored procedure, a_date date and atime varchar2. In table both fields are date type. I want to store complete data and time in the atime field. I am passing adate as 01-Jun-2018 format and time as 17:12:12. In stored procedure I used the following in the insert statement and it works fine:

to_date( to_char(a_date,'DD-MM-YYYY') || ' ' || to_char(to_date(p_time,'HH24:MI:SS'),'HH:MI:SS'), 'DD-MM-YYYY HH24:MI:SS' ),

SQL> create or replace procedure
2 happychristmas
3 ( p_date IN date,
4 p_wishes OUT varchar2)
5 is
6 v_wishes number(23);
7 begin
8 select count(1) into v_wishes from wish_obj where wish_date=to_date(P_DATE);
9 if (v_wishes>0)
10 then
11 INSERT INTO COUNTING_WISHES
12 (WISHES_CNT,
13 WISHES_FOR,
14 WISHING_DATE)
15 VALUES
16 (WISHIDSEQ.NEXTVAL,
17 V_WISHES,
18 P_DATE);
19 COMMIT;
20 p_wishes:='HAPPY CHRISTMAS';
21 else
22 p_wishes:='YOU FORGOT CHRISTMAS DATE..HA HA HA';
23 end if;
24 END;
25 /

Procedure created

SQL> exec happychristmas('25-DEC-19');

begin happychristmas('25-DEC-19'); end;

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HAPPYCHRISTMAS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> create or replace
  2  procedure MY_PROC(d date, o out int) is
  3  begin
  4    o := 1;
  5  end;
  6  /

Procedure created.

SQL>
SQL> exec my_proc(sysdate)
BEGIN my_proc(sysdate); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MY_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
SQL> variable g number
SQL> exec my_proc(sysdate,:g)

PL/SQL procedure successfully completed.

SQL> print g

         G
----------
         1