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

I dont understand your reply.

1) Yes, you right. su.usage_date is a timestamp.
2) Y should only records that occured exactly at midnight be included
and all others excluded? The comparision is supposed to include all
records for Nov 30.
3) I have been able to fix it by cast the su.usage_date to DATE. But
am still confused on y it should fail without the cast.

NB. I have your book. I would be gratefull if u would point me to the
right place for more information, cos i have a lot of such queries.

Tx 4 d reply n d great book.

Have a wonderfull New Year.