Subject [firebird-support] Re: Question on Month date range in SELECT statements
Author Svein Erling Tysvaer
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.


-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