Date Parameters and Procedures

[cbh35711]

I've got a procedure that pulls data from a table based on a date within the table. I put in two parameters when running the procedure, a start and end date(my first procedure with parameters). I can't figure out how to get the format right on the date.
here's the if statement

if rbt_rec.PaidDate between to_date(start_date,'mm-dd-yyyy') and to_date(end_date,'mm-dd-yyyy') then
...
end if;

and then here's the pl/sql block

DECLARE
  START_DATE DATE;
  END_DATE DATE;
BEGIN
  START_DATE := '10-01-2006';
  END_DATE := '12-31-2006';

  UPDATE_MC_REBATE_CSV(
    START_DATE => START_DATE,
    END_DATE => END_DATE
  );
END;

I get this error:
ORA-01843: not a valid month
ORA-06512: at line 5

I've tried changing the date to every format I can think of but it's still giving me the error. I even went so far to match the date in the data, which is dd-mmm-yy, but still nothing. It's got to be something easy, but i can't figure it out.


Thanks for your help,

Chris

 

[Thargy]

cbh,

after the BEGIN you have

START_DATE := '10-01-2006';

You are attempting to cast a string to a date. State the date format, so:-

START_DATE := TO_DATE('10-01-2006','DD-MM-YYYY');

This means that you will call your update routine with a date instead of a string literal.

Regards

Tharg


Grinding away at things Oracular

 

[cbh35711]

Thanks Tharg. I'm still getting an error, a different one now though...progression.
Changed the block to

  START_DATE := TO_DATE('10-01-2006','mm-dd-yyyy');
  END_DATE := TO_DATE('12-31-2006','mm-dd-yyyy');

Error Message:
ORA-01861: literal does not match format string
ORA-06512: at "procedure", line 46
ORA-06512: at line 8

Line 46 is the if statement, but the date formats match

if rbt_rec.PaidDate between to_date(start_date,'mm-dd-yyyy') and to_date(end_date,'mm-dd-yyyy') then

Thanks for your help,

Chris

 

[Thargy]

cbh,

I'm not sure, but change the date format mask to upper case. I think[/] it may be case sensitive.

T

Grinding away at things Oracular

 

[SantaMufasa]

Chris,

Your problem is rather cyclical at this point...your PL/SQL code is asking Oracle to convert "START_DATE", which is already a DATE data type, into a DATE data type. Since START_DATE and END_DATE are already dates, your code should now look like this:

if rbt_rec.PaidDate between start_date and end_date then...

Let us know if this makes life easier for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

cbt,

Santa is saying what I was just about to, if you've converted to a date, then further 'TO_DATE'ing isn't needed.

Dave, can I pass this one to you, as its gone 10 here, and I'm fading fast?

T

Grinding away at things Oracular

 

[cbh35711]

Thanks guys. Sadly, I'm still getting the same problem.
if statement

if rbt_rec.PaidDate between start_date and end_date then

pl/sql block

  START_DATE := TO_DATE('10-01-2006','mm-dd-yyyy');
  END_DATE := TO_DATE('12-31-2006','mm-dd-yyyy');

Same exact error.

I tried changing the IN dates to match the format of the dates in the table, 'dd-mmm-yy' so 01-OCT-06, but it said that wasn't a recognized format.

Thanks again guys,

Chris

 

[carp]

If you are passing a value of '01-OCT-06', then your date format should be 'dd-MON-yy' (or better yet, 'DD-MON-YY' to promote better readability).

 

[cbh35711]

Excellent, thanks carp that worked. MMM!=MON sigh.


Thanks again everyone,

Chris