ORACLE 10g : To_date() Not a valid month

Question

So I have this little script :

set serveroutput on;
DECLARE
  val DATE;
BEGIN
  val := TO_DATE('27-Jan-2001','DD-Mon-YYYY');
  dbms_output.put_line(val);
END;

The mask match with the date I'm inputing but it displays

ORA-01843: not a valid month

every time...

Thanks

Try `val := TO_DATE('27-JAN-2001','DD-MON-YYYY');` - see if it works. Also, checkout the NLS parameters of the database, by issuing `show parameter nls`. Paste the result here. — g00dy, May 12 '17 at 13:46
My nls_date_format is DD/MM/RR Same error with val := TO_DATE('27-JAN-2001','DD-MON-YYYY') — Scarrs, May 12 '17 at 13:51
What do you get by `select value from nls_session_parameters where parameter = 'NLS_DATE_LANGUAGE';` - I assume it is neither "american" nor "english". — Wernfried Domscheit, May 12 '17 at 13:54

score 6 · Accepted Answer · answered May 12 '17 at 13:57

6

Most likely your current session NLS_DATE_LANGUAGE is set to a value where January is not abbreviated as "Jan".

Try this:

DECLARE
  val DATE;
BEGIN
  val := TO_DATE('27-Jan-2001','DD-Mon-YYYY', 'NLS_DATE_LANGUAGE = american');
  dbms_output.put_line(val);
END;

Or execute

ALTER SESSION SET NLS_DATE_LANGUAGE = 'american';

before you run your PL/SQL block.

answered May 12 '17 at 13:57

Thanks it worked with the 'NLS_DATE_LANGUAGE = american' parameter! – Scarrs May 12 '17 at 14:09

1 Answers1