CODEWITHSUNDEEP
oracle-databaseplsqltype-conversionora-01830
Salik
Salik

Reputation: 518

Oracle 01830 error while converting string to date in oracle procedure

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..

Upvotes: 1

Views: 12791

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 1

Related Questions