RE: date difference

"Zabach, Elke" <[email protected]> Thu, 4 Sep 2003 09:17:19 +0200

Newsgroups	gmane.comp.db.sapdb.general
Message-ID	<[email protected]>

 jain cj wrote:

> Hi,
> 
> Here I am facing one problem. I want to find the date 
> difference like this
> 
> SELECT
>        uf.*
> FROM
>       SI_UnavailableFund_v uf,  SI_BusinessDate bd
> WHERE
>       (bd.BusinessDate - (NVL( uf.ExpiryDate, TO_DATE( '01-01-2300', 
> 'MM-DD-YYYY' ))) < 0)
> 
> but it is not allowing NVL here. (Oracle mode)
> 
> Internal mode is  supporting 'DATEDIFF' but it will give +ve 
> value always no 
> <0 .
> 
> so how can find the date difference (+ve and -ve not absolute value).

What do you think about  (Oracle mode)
WHERE
     MONTHS_BETWEEN  (bd.BusinessDate - 
                                       NVL( uf.ExpiryDate, TO_DATE( '01-01-2300', 'MM-DD-YYYY' ))) < 0

Elke
SAP Labs Berlin