Error:months_between for dd-mm-yy format

[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

[thomasp]

select months_between (to_date('01-02-80','DD-MM-YY'),to_date('01-09-80','DD-MM-YY'))
from dual;

[tamilselvan]

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

[slimdave]

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

[WilliamR]

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.

[mike9]

>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?

[WilliamR]

It's an ANSI date literal.