-
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
-
Code:
select months_between (to_date('01-02-80','DD-MM-YY'),to_date('01-09-80','DD-MM-YY'))
from dual;
thomasp
-
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
-
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
-
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?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|