• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1072
  • Last Modified:

Invalid month error when executing stored procedure

Hello. The date field I am trying to filter on stores data in the form:

23-DEC-08

For some reason, when I execute a stored proc like this:
EXEC procname (to_date('01/03/11','DD-mon-YY'),to_date('31/03/11','DD-mon-YY'))

Open in new window


The stored proc is like this (pseudocode):

create or replace
PROCEDURE procname (From_date IN date, T_date IN date)
IS      

CURSOR results_cur IS

select statement
from tablename
where (tablename.DATERESOLVED>=TO_DATE (From_date,'DD-MON-YY') AND tablename.DATERESOLVED<TO_DATE (T_date,'DD-MON-YY'));

------------

The error I get is:

ORA-01843: not a valid month
ORA-06512: at line 1
01843. 00000 -  "not a valid month"

Any idea why this would be? The format mask matches the format of the data in the table. So I am at a loss as to why it doesn't see the month is valid. Am I missing something here? Thanks for any guidance.
0
dprasad
Asked:
dprasad
  • 6
  • 3
  • 3
1 Solution
 
sdstuberCommented:
TO_DATE (From_date,'DD-MON-YY')


from_date is a date,  so you're converting a date to a date
which means you're doing an implicit conversion from a date to a string, in order to give to_date something to use

same with your t_date parameter

just remove the TO_DATE calls
0
 
sdstuberCommented:
also...

while this is legal

to_date('01/03/11','DD-mon-YY'),

it would make more sense if it were

to_date('01/03/11','DD-MM-YY')

0
 
dprasadAuthor Commented:
ok thanks. So here is the updated version of what I have now in the stored proc:

(tablename.DATERESOLVED>=From_date AND tablename.DATERESOLVED<T_date)


And executing like this:
SET SERVEROUTPUT ON;
EXEC SODEXO_APPEALRESOLUTION (to_date('01-MAR-11','dd-MON-yy'),to_date('31-MAR-11','dd-MON-yy'))

Open in new window

The procedure does run without error, but no results are returned. I know for a fact there is data there for that range in the query, Because, if I alter the procedure date like so:

(tabename.DATERESOLVED>='01-MAR-11' AND tablename.DATERESOLVED<='31-MAR-11');

Open in new window


and execute it the same way, I do receive the proper results.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
sventhanCommented:

try this...

EXEC procname (to_char('01/03/11','DD-mon-YY'),to_char('31/03/11','DD-mon-YY'))

and your stored proc will be

create or replace
PROCEDURE procname (From_date IN date, T_date IN date)
IS      

CURSOR results_cur IS

select statement
from tablename
where (tablename.DATERESOLVED>=TO_DATE (From_date,'DD-MON-YY') AND tablename.DATERESOLVED<TO_DATE (T_date,'DD-MON-YY'));
0
 
sventhanCommented:
sorry! Your stored proc will be like this...

create or replace
PROCEDURE procname (From_date IN varchar2, T_date IN varchar2)
IS      

CURSOR results_cur IS

select statement
from tablename
where (tablename.DATERESOLVED>=TO_DATE (From_date,'DD-MON-YY') AND tablename.DATERESOLVED<TO_DATE (T_date,'DD-MON-YY'));
0
 
sdstuberCommented:
dprasad,

>>>  (tabename.DATERESOLVED>='01-MAR-11' AND tablename.DATERESOLVED<='31-MAR-11');

this is also doing an implicit date conversion.



sventhan,

>>>  (to_char('01/03/11','DD-mon-YY')

this is the reverse problem originally identified,  this converts a string to a string, which means you have to do an implicit date conversion first.


try this...  
use DATE types as DATE types and no conversions at all
efficient and simple

create or replace
PROCEDURE procname (From_date IN date, T_date IN date)
IS      

CURSOR results_cur IS

select statement
from tablename
where tablename.DATERESOLVED > From_date
   AND tablename.DATERESOLVED< T_date;
0
 
sdstuberCommented:
invoke with the parameters as you did in http:#35733378
0
 
dprasadAuthor Commented:
sdstubber: Thanks. The procedure runs to completion without error now. But I am not getting any output value. Here is the whole stored procedure:

create or replace
PROCEDURE STOREDPROC (From_date IN DATE, T_date IN DATE)
IS      

CURSOR results_cur IS

 SELECT ((sum((CASE WHEN V_ISSUE.DATERESOLVED<=V_ISSUE.DUEDATE THEN 1 ELSE 0 END)))/COUNT(V_ISSUE.PKEY))*100 SLA_PERCENT        
 FROM   TABLENAME V_ISSUE
 WHERE  V_ISSUE.PROJECT_CODE='SDXJ' AND
 V_ISSUE.TYPE=1 AND
 V_ISSUE.CATEGORY_NAME LIKE 'Appeals/Lvl 1%' AND
 V_ISSUE.ISSUESTATUS<>4 AND
 (V_ISSUE.DATERESOLVED>=From_date AND V_ISSUE.DATERESOLVED<T_date);

results_rec results_cur%rowtype;


BEGIN

      FOR results_rec in results_cur
 
        LOOP
            DBMS_OUTPUT.PUT_LINE(results_rec.SLA_PERCENT);




END LOOP;

END;

I should see the number 100 as output, thats what the SQL query produces by itself. Is there something wrong with how I'm referencing the sql results in the dbms ouput?
0
 
dprasadAuthor Commented:
Ok that worked thanks again you rock!!!!
0
 
sdstuberCommented:
can you post exactly what you are running for the standalone SQL statement

as well as your invocation of the procedure?


a sqlplus spool of both from same session would be ideal
0
 
sdstuberCommented:
glad I could help

you must have been including an implicit conversion somewhere.

easy rule to remember  - follow it always, and you'll never have those kinds of problems, or if you do, they'll be self-documenting

DATES are DATES,  STRINGS are STRINGS - don't convert unless you have to, but when you do ALWAYS use explicit conversion with a format mask
0
 
sventhanCommented:
Thanks SD and I stand correct.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now