date functions

[ON]

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