Re: Comparing dates

On 3/6/01, 4:38:41 PM, <patrick(dot)jacquot(at)anpe(dot)fr> wrote regarding Re: [SQL]
Comparing dates:

> Markus Fischer wrote:
> > I've a SELECT statement on many joined Tabled and one of them has
> > a date column called 'date_date'. When I fetch a date e.g.
> > '02-03-2001', I get, say, 60 results back. When I now perform the
> > same query with another date, lets take '03-03-2001', I get back
> > about 70 results.
> >
> > When I now modify my query to get both results in one I write
> >
> > SELECT
> > ....
> > FROM
> > ..
> > AND
> > date_date >= '2001-03-02'
> > AND
> > date_date <= '2001-03-03'
> > AND
> > ....
> >
> > I think I should get back the rows for both days, 60 + 70 makes
> > 130 to me. But what I get back is even smaller then 60. I
> > allready tried TO_DATE conversion, an OR construct but always
> > the same result.

> ANDing restrictions makes them narrower.
> If you want to broaden your selection, try ORing the conditions
> HTH
> Patrick

True enough - but in this case he should still get two days' worth.
There's something odd here - try

AND date_date >= '2001-03-02'::date
AND date_date <= '2001-03-03'::date

Or even

AND (date_date = '2001-03-02'::date OR date_date = '2001-03-03'::date)

And see if that helps. The only thing I can think of is that either
date_date or the comparisons are being used as a timestamp and there is
an invisible time part in there skipping some of the entries.

Actually, try:

AND date_date >= '2001-03-02' AND date_date < '2001-03-04'

(note the < on the second part) If that does it, there are times in there
somewhere

- Richard Huxton