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

Answer 1

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.