Error:months_between for dd-mm-yy format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Error:months_between for dd-mm-yy format

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Error:months_between for dd-mm-yy format

    How to use months_between function if dates are in dd-mm-yy
    format.The function accepts only dd-mon-yy format.

    SQL> select months_between('01-02-80','01-09-80')
    2 from dual;
    select months_between('01-02-80','01-09-80')
    *
    ERROR at line 1:
    ORA-01843: not a valid month


    This works:
    1 select months_between('1-FEB-80','20-FEB-65')
    2* from dual


    MONTHS_BETWEEN('1-FEB-80','20-FEB-65')
    --------------------------------------
    179.387097

  2. #2
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Code:
    select months_between (to_date('01-02-80','DD-MM-YY'),to_date('01-09-80','DD-MM-YY'))
    from dual;
    thomasp

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by javamaid
    How to use months_between function if dates are in dd-mm-yy
    format.The function accepts only dd-mon-yy format.

    SQL> select months_between('01-02-80','01-09-80')
    2 from dual;
    select months_between('01-02-80','01-09-80')
    *
    ERROR at line 1:
    ORA-01843: not a valid month


    This works:
    1 select months_between('1-FEB-80','20-FEB-65')
    2* from dual


    MONTHS_BETWEEN('1-FEB-80','20-FEB-65')
    --------------------------------------
    179.387097
    Have you ever heard of Y2K problem?

    Tamil

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by javamaid
    The function accepts only dd-mon-yy format....
    What it accepts is a date data type. If you supply a character string then Oracle will attempt an implicit conversion using the session's value of nls_date_format. The problem with this is that it can be changed system-wide or by the client, so the best practice (indeed the only acceptable practice) is to always use an explicit conversion.

    If you are going to insist on two-character years then use the RR format to convert, and consult the docs to understand what it and other format elements mean http://download-west.oracle.com/docs....htm#sthref429
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    We are assuming that years 80 and 65 must be in the previous century because they are in the range that usually is. Perhaps they really are 2080 and 2065.

    Another convenient way to specify a date literal is

    DATE '2080-02-01'

    This accepts only one format (YYYY-MM-DD), literals only.

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    >Another convenient way to specify a date literal is
    >DATE '2080-02-01'
    >This accepts only one format (YYYY-MM-DD), literals only.

    what is this?

  7. #7
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159

Posting Permissions

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


Click Here to Expand Forum to Full Width