Printable View
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
Code:select months_between (to_date('01-02-80','DD-MM-YY'),to_date('01-09-80','DD-MM-YY'))
from dual;
Have you ever heard of Y2K problem?Quote:
Originally Posted by javamaid
Tamil
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.Quote:
Originally Posted by javamaid
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
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.
>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?
It's an ANSI date literal.