Re: Date searching by month & day only - Mailing list pgsql-novice

On 03/13/2012 02:42 PM, Chris Campbell wrote:
>
> Hello List,
>
> I’m attempting to pull up a birthday list of anyone born between,
> let’s say June 15 and June 30^th . The year is irrelevant.
>
> I’m wondering if there is a more elegant way to do this than using
> something like:
>
> SELECT key, dob from ds1.contact
>
> WHERE
>
> extract(month FROM contact.dob ) = 6
>
> and extract(day FROM contact.dob) >= 15
>
> and extract(day FROM contact.dob) <= 30
>
>
One thing to consider is whether the rest of your query combined with
the size of your table will require the ability to use an index to speed
the query.

Here is one possibility

SELECT ... WHERE to_char(contact.dob, 'MMDD') between '0615' and '0630'...

If necessary, you can index on (to_char(contact.dob, 'MMDD')).

Cheers,
Steve