Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Ora-01843 Not a valid month

    I have not used PL/SQL in years and am having problems with this query. I am just trying to select records between two dates and keep getting the Not a valid month error. Here is the select statement

    Code:
    SELECT ord_hdr_seq_no,ord_hdr_sts_cd
    FROM order_headers
    WHERE idclinicnumber = 23075
    and (last_chg_date >= '04/01/2013' and last_chg_date <= '04/30/2013') ;
    Any help would be greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The purpose of TO_DATE is to convert string datatype to DATE datatype.
    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 04/30/2013, not a date'
    When a DATE datatype is desired, then use TO_DATE() function including format.

    '10-11-12'
    Which is correct DATE below for string above?
    Oct. 11 2012
    Nov. 10 2012
    Nov. 12 2010
    Dec. 11 2010
    Oct. 12 2011
    Dec. 10 2011
    I'll give you 6 guesses, since the first 5 will be incorrect.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Quote Originally Posted by cedwards65 View Post
    Any help would be greatly appreciated.
    If I'm not mistaken you can put the date(s) in quotes in the format of your 'nls_date_format' without having to use the "to_date" function, for example:
    Code:
    select sys_context('userenv','nls_date_format') as nls_date_format from dual;
    nls_date_format
    DD-MON-RR
    
    select ord_hdr_seq_no, ord_hdr_sts_cd
     from  order_headers
    where  idclinicnumber  =  23075
     and   last_chg_date between '01-apr-2013' and '30-apr-2013';

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by spacebar View Post
    If I'm not mistaken you can put the date(s) in quotes in the format of your 'nls_date_format' without having to use the "to_date" function, for example
    Which is very bad coding style (and a bad advice as well) because the nls_date_format setting is a client setting.
    So the SQL might run for one connection but not the other.

    If you really want to avoid the to_date() function (e.g. for compatibility reasons), then use an ANSI date literal, e.g. DATE '2013-04-30'.
    An ANSI literal always has to specify the date in ISO format (YYYY-MM-DD) so there is no ambiguity
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Anyway, when date has a time component, we're back to TO_DATE.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    Anyway, when date has a time component, we're back to TO_DATE.
    In that case you can use an ISO timestamp literal: timestamp '2013-06-02 19:30:10'
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Aha! That's something new! I knew about the DATE date literal, but TIMESTAMP's new for me. Thank you!

Posting Permissions

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