With difference comparison, no index can be used anyway. But if the case
is like Helen wrote:

where extract (month from aTimestamp)
= extract (month from CURRENT_TIMESTAMP)
and extract (year from aTimestamp)
= extract (year from CURRENT_TIMESTAMP)

then why not simply do this:

where extract(month from aTimestamp)
= extract(month from CURRENT_TIMESTAMP)
and aTimestamp between CURRENT_TIMESTAMP-32 and CURRENT_TIMESTAMP+32

BETWEEN can use an index and limits the result set (no month is longer
than 32 days - even if one happen to use a strange form for summer
time), and EXTRACT ascertains that only records from the current month
is included.

HTH,
Set

-This mail was sent around 07:12 GMT, wonder when/if it will reach the list?

Anderson Farias wrote:

> Michael D. Spence wrote:
>
>> You want EXTRACT. This works for me:
>>
>> select count(*) from sometable
>> where EXTRACT(MONTH from timestampfield)
>> <> Extract(MONTH from CURRENT_DATE)
>>
>
> Althougt this is true, the problem here is that extract won=B4t let fb=20
> use an index for the search and, as stated initially the table has=20
> milions of records so this can be very ineffective.
>
> a between 'first-day-of-month' and 'first-day-of-next-month' whould=20
> be more effient. or, have a 'month' collum filled by triggers.
>
> just a thought
>
> Regards,
> Anderson Farias