918440 wrote:Usuaully, when people talk about dates "from x to y", x is earlier than y. You're setting v_from_date to be over 5 years later than v_to_date. That's not the cause of the ORA-01841 error, or any other Oracle error, but it is likely to confuse whoever has to maintain this code.
I have two statement in my procedure :
r_period IN VARCHAR2
v_from_date := to_date('1/4/'||to_char(r_period),'dd/mm/yyyy');
v_to_date := to_date('31/3/'||to_char((to_number(r_period)-5)),'dd/mm/yyyy');
when I ran procedure , got following error message:That's one of those error messages that actually means what it says. Apparantly, you're calling the procedure with a bad value for r_period. I can't say anthing more specific until you post a complete script.
"ORA-01841 :(full) year must be between -4713 and +9999, and not be 0 "
Personally, I would do it this way:
v_to_date := ADD_MONTHS ( TO_DATE ( '31/3/' || r_period , 'DD/MM/YYYY' ) , -5 * 12 );
which also uses only 1 conversion, and that conversion is very simple. You can still get errors, including ORA-01841, if you pass a bad value for r_period, but the errors will be easier to find and fix.
v_to_date := ADD_MONTHS ( TRUNC ( TO_DATE (r_period, 'YYYY') , 'YEAR' ) - 1 , -57 );