Re: date functions

From: ON <aufron_at_wanadoo.fr>
Date: Tue, 1 Jul 2003 20:16:26 +0200
Message-ID: <bdsj5m$el6$1_at_news-reader3.wanadoo.fr>

[Quoted] Hi all,

[Quoted] [Quoted] I think that the question is not quite clear. So I guest that it concerns the available prices to be applied for May of the current year.

The solution consist to solve the following logical statements :

Statement A : effective_start_date is before May of the year
Statement B : effective_start_date is in May of the year
Statement C : effective_end_date is in May of the year
Statement D : effective_end_date is after May of the year

The selected prices must satisfy this date interval condition : (A and C) or (A and D) or (B and C) or (B and D)
that can be covered by : (A and (C or D)) or (B and (C or D))

So try this :

select price,effective_start_date,effective_end_date

    from pricing
    where

(to_char(effective_start_date,'YYYYMM') < '200305'
        and (to_char(effective_end_date,'YYYYMM') = '200305' or to_char(effective_end_date,'YYYYMM') > '200305'))

        or


        (to_char(effective_start_date,'YYYYMM') = '200305'


        and (to_char(effective_end_date,'YYYYMM') = '200305' or

to_char(effective_end_date,'YYYYMM') > '200305'))

Regards Received on Tue Jul 01 2003 - 20:16:26 CEST