Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Location
    Romania, Bucarest
    Posts
    5

    Question Unanswered: ORA-01843: not a valid month

    Hi.
    I tried to enter this code but it gave me the "ORA-01843: not a valid month" error. What should i do?

    INSERT INTO employees VALUES
    ( 100
    , 'Steven'
    , 'King'
    , 'SKING'
    , '515.123.4567'
    , TO_DATE('17-JUN-1987', 'dd-MON-yyyy')
    , 'AD_PRES'
    , 24000
    , NULL
    , NULL
    , 90
    );

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What is the current value for NLS_LANGUAGE in your session?
    Is JUN a valid abbreviation in that language for the month June?

  3. #3
    Join Date
    May 2010
    Location
    Romania, Bucarest
    Posts
    5
    i user

    select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

    and the result was

    PARAMETER VALUE
    NLS_DATE_FORMAT DD-MM-RRRR

  4. #4
    Join Date
    May 2010
    Location
    Romania, Bucarest
    Posts
    5
    i tried with


    INSERT INTO employees VALUES
    ( 100
    , 'Steven'
    , 'King'
    , 'SKING'
    , '515.123.4567'
    , TO_DATE('17-06-1987', 'dd-MON-yyyy')
    , 'AD_PRES'
    , 24000
    , NULL
    , NULL
    , 90
    );


    but again it gives me the same error

  5. #5
    Join Date
    May 2010
    Location
    Romania, Bucarest
    Posts
    5
    i found the problem

    NLS_DATE_FORMAT DD-MM-RRRR
    NLS_DATE_LANGUAGE ROMANIAN


    :|

    now... how do i change that language?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dumitruoana View Post
    ....
    , TO_DATE('17-06-1987', 'dd-MON-yyyy')
    ....
    but again it gives me the same error
    Of course it does, because 06 is not a valid month name abbreviation.
    You have to use 'DD-MM-YYYY' to indicate that you are passing a number.

    The format string is well documented in the manual:
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

    now... how do i change that language?
    You can use ALTER SESSION SET NLS_LANGUAGE to change the language, but I would strongly recommend to not rely on any language setting.

    If you can you should use language-neutral literals. e.g. TO_DATE('17-06-1987', 'dd-mm-yyyy')

    Or use the ANSI standard for date literals: DATE '1987-06-17'
    which will also work with other DBMS that support the ANSI standard.

  7. #7
    Join Date
    May 2010
    Location
    Romania, Bucarest
    Posts
    5
    thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •