1

Following is the first few lines of my stored procedure in oracle. All I am trying to do is to pass a string into procedure, convert it into date and use it in a query. But it doesn't seem to work. Currently, the error I am being thrown at is as following:

ORA-01830: date format picture ends before converting entire input string

ORA-06512: at "APPS.PORDUCTPLANNINGFORECAST", line 26

ORA-06512: at line 1

CREATE OR REPLACE PROCEDURE APPS.PorductPlanningForecast (
   vDateFrom        IN varchar2,
   vDateTo          IN varchar2 , 
   vForecastSetDPL2 IN varchar2, 
   out              SYS_REFCURSOR
)
IS 
  L_CURSOR SYS_REFCURSOR; 
  vfrom    date; 
  vto      date;
BEGIN
  vfrom:= TO_DATE(vDateFrom,'DD/MM/YYYY HH24:MI:SS');
  vto:=TO_DATE(vDateTo,'DD/MM/YYYY HH24:MI:SS');

the lines having TO_DATE() are line 26 and 27. Also, the format I am passing in is through c# which is System.DateTime format("01/08/2013 12:00:00 AM") converted into a string and then passed through add parameter as gave up on passing date as date due to date conversion errors. Please help..

Community
  • 1
  • 1
Salik
  • 508
  • 6
  • 17
  • 35
  • tried changing nls parameter setting setting it to the format provided in to_date but it didnt work. – Salik Aug 30 '13 at 08:06
  • 1
    Try: to_date( string, 'DD/MM/YYYY HH:MI:SS AM" ) – krokodilko Aug 30 '13 at 08:14
  • how do i keep it variable?AM/PM? also now i am getting the following error: ORA-01858: a non-numeric character was found where a numeric was expected – Salik Aug 30 '13 at 08:16

1 Answers1

1

If you are passing in a string of the following format 01/08/2013 12:00:00 AM then in order to successfully convert that string into a date datatype you should use the following format mask 'dd/mm/yyyy hh:mi:ss AM' which includes meridian indicator:

to_date(vDateFrom, 'dd/mm/yyyy hh:mi:ss AM')

but how do i make it variable?it could be PM too

Meridian indicators are interchangeable. For both strings 01/08/2013 2:00:00 AM and 01/08/2013 2:00:00 PM for instance, you can use date format model with one of the meridian indicators, whether it AM or PM. Here is an example:

select to_date('01/08/2013 2:00:00 AM', 'dd/mm/yyyy hh:mi:ss AM') as res
  from dual

Result:

Res 
-----------
01.08.2013 2:00:00 


select to_date('01/08/2013 2:00:00 PM', 'dd/mm/yyyy hh:mi:ss AM') as res
  from dual

Res 
-----------------
01.08.2013 14:00:00
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • And now it yields the following error: ORA-01858: a non-numeric character was found where a numeric was expected – Salik Aug 30 '13 at 08:15
  • thank you, could you also help me with a subsequent error? ORA-01858: a non-numeric character was found where a numeric was expected – Salik Aug 30 '13 at 09:16
  • @user1451836 Would be nice if you provided more detailed information about under what circumstances that exception is raised. – Nick Krasnov Aug 30 '13 at 09:19
  • Right after making the changes as : `BEGIN vfrom:= TO_DATE(vDateFrom,'dd/mm/yyyy hh:mi:ss AM'); vto:=TO_DATE(vDateTo,'dd/mm/yyyy hh:mi:ss AM');` I got the error of ORA-01858: a non-numeric character was found where a numeric was expected – Salik Aug 30 '13 at 09:20
  • I am passing them in String variable.it originates from a MySQL date type converted and stored as string in a string variable and then sent in here. – Salik Aug 30 '13 at 09:35
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/36530/discussion-between-nicholas-krasnov-and-user1451836) – Nick Krasnov Aug 30 '13 at 10:18