At 04:08 AM 23/12/2005, you wrote:

>I am receiving inconsistent results from almost the same query that i
>am running.
>
>Below is the query.
>
>==================
>part 1:
>
>select distinct su.card_id, su.usage_date
>from scratch_card_usage su
>join scratch_cards sc on su.card_id = sc.id
>where sc.card_type_id in (9, 10)
>and (su.usage_date <= '30-dec-2005' and su.usage_date >= '1-dec-2005')
>
>union
>
>select distinct su.card_id, su.usage_date
>from scratch_card_usage su
>join scratch_cards sc on su.card_id = sc.id
>where sc.card_type_id in (9, 10)
>and (su.usage_date <= '30-nov-2005' and su.usage_date >= '1-nov-2005')
>
>========================
>
>
>========================
>part 2:
>
>select distinct su.card_id, su.usage_date
>from scratch_card_usage su
>join scratch_cards sc on su.card_id = sc.id
>where sc.card_type_id in (9, 10)
>and (su.usage_date between '1-nov-2005' and '30-dec-2005')
>
>
>========================
>Problem:
>The records returned by both queries are not the same. I have gone
>thru it like a 100 times cant find out where am going wrong. Is the
>query wrong or is there a problem with Firebird? I have attached a
>backup of the database as, "odd_behavious.fbk". Please i would
>appreciate any response.
>
>
>Specs:
>1. Firebird 1.5.4172
>2. Windows XP
>3. IB Expert

My guess here is that su.usage_date is a timestamp type, not a date type.
The first query will exclude all records on Nov 30 except any that occurred
exactly at midnight. The second query will include those records. Neither
query will include any records after midnight on Dec. 30.

./heLen