Re: date functions

"ON" <aufron_at_wanadoo.fr> wrote in message news:<bdsj5m$el6$1_at_news-reader3.wanadoo.fr>...
> Hi all,
>
> 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

It's better to use trunc function:

where
 (trunc(effective_start_date, 'MM') <= to_date('20030501', 'YYYYMMDD')   and trunc(effective_end_date, 'MM') >= to_date('20030501', 'YYYYMMDD'))

And learn to use <= and >= operators, it saves a lot of time and reduces amount of code Received on Wed Jul 02 2003 - 18:59:31 CEST