Re: months_between function

Thomas,

Our client is windows NT and we are using SQL NET. The nls_date_format is 'yyyy-mm-dd'.

Depending the date we are using the query will work or not:

select months_between('1996-02-01','1996-11-15') from dual; // WORKS
select months_between('1996-02-11','1996-11-11') from dual; // DOES NOT WORK

To be sure that the problem is not with the nls_date_format, i ran the querys from a table (select months_between(date_a,date_b) from tablea) and got the same results.

I've got the same results with a store procedure.

--
Eric St-Pierre
CPD5 Associate
ericst_at_mediom.qc.ca

> Do you have more information? (whats the client, what api are you using,
whats
> your nls_date_format, and so on)...
>
> I just ran your example, no problems once I changed the nls_date_format:
>
> SQL> select months_between('1996-02-01','1996-11-15')
> 2 from dual;
> ERROR:
> ORA-01843: not a valid month
>
>
>
> no rows selected
>
> SQL> alter session set nls_date_format = 'yyyy-mm-dd';
>
> Session altered.
>
> SQL> select months_between('1996-02-01','1996-11-15')
> 2 from dual;
>
> MONTHS_BETWEEN('1996-02-01','1996-11-15')
> -----------------------------------------
> -9.4516129
>
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>

>

> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
>
Received on Thu Nov 12 1998 - 14:04:08 CST