ORA-01843: not a valid month

[Lucindar]

Could someone PLEASE help me with this. Yes, it is a school assignment. But I have been working on it for a week and it is due today - I have searched everywhere and just cannot get past this. Any help at all is appreciated so much.


1 ACCEPT datvar1 date format 'MM-DD-YYYY' PROMPT 'Please enter the low date range :'
2 ACCEPT datvar2 date format 'MM-DD-YYYY' PROMPT'Please enter the high date range :'
3 define datvar1 = "01-01-1981" (char)
4 define datvar2 = "01-01-1982" (char)
5 select *
6 from emp
7* WHERE hiredate between '&datvar1' and '&datvar2'
SQL> START "C:\SCHOOL\P8Q3.SQL"
Please enter the low date range :01/01/1981
Please enter the high date range :01/01/1982
old 3: WHERE hiredate between '&datvar1' and '&datvar2'
new 3: WHERE hiredate between '01-01-1981' and '01-01-1982'
WHERE hiredate between '01-01-1981' and '01-01-1982'
*
ERROR at line 3:
ORA-01843: not a valid month

 

[SantaMufasa]

Lucindar,

The problem is that your date input is a character string whose format does not match your installation's default format (probably 'DD-MON-YY'). Therefore, to resolve your problem, you must tell Oracle to convert your character string into a DATE expression in this fashion:

select * from emp
 WHERE hiredate between to_date('&datvar1','mm-dd-yyyy')
                    and to_date('&datvar2','mm-dd-yyyy');

Let us know if this fixes your problem.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Lucindar]

Yes, it WORKS! You have my undying gratitude. I feel I can proceed now with the remainder of this question and build on the code since you helped me get past this.

You were correct the format for the date is different but I could not figure out how to fix it.

Thanks again.

 

[Beilstwh]

Just curious, why did you use a define after the accept. The accept creates a variable. The define overrides it. Your script should be


ACCEPT datvar1 date format 'MM-DD-YYYY' PROMPT 'Please enter the low date range :'
ACCEPT datvar2 date format 'MM-DD-YYYY' PROMPT'Please enter the high date range :'
select *
from emp
WHERE hiredate between to_date('&datvar1','MM-DD-YYYY') and TO_DATE('&datvar2','MM-DD-YYYY');

Bill
Oracle DBA/Developer
New York State, USA

 

[Lucindar]

Yep....

Overkill was my problem all along. I ended up taking out the DEFINE. Here is the code that completed my assignment. I appreciate the help that I received.

ACCEPT datvar1 date format 'MM-DD-YYYY' PROMPT 'Please enter the low date range("MM/DD/YYY"):'
ACCEPT datvar2 date format 'MM-DD-YYYY' PROMPT'Please enter the high date range("MM/DD/YYY"):'
select ename||', '||job EMPLOYEES, hiredate HIREDATE
from emp
WHERE hiredate between to_date('&datvar1','mm-dd-yyyy')
and to_date('&datvar2','mm-dd-yyyy')
/