Thread: Date searching by month & day only

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

>> 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
touse 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

I like it!  Thank you Steve for your suggestion.


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice