Graeme St. Clair [mailto:[EMAIL PROTECTED] wrote: > Oracle 8.1. > > I'm finding that for a REQUEST_DATE value of 25-May-01:- > > WHERE BLAH.REQUEST_DATE <= TO_DATE('25-MAY-01', 'DD-MON-YY') > > yields nothing, while > > WHERE BLAH.REQUEST_DATE >= TO_DATE('25-MAY-01', 'DD-MON-YY') > > spits out the rows I want. > > > WHERE BLAH.REQUEST_DATE BETWEEN TO_DATE('25-MAY-01', 'DD-MON-YY') > AND TO_DATE('26-MAY-01', 'DD-MON-YY') > > also works, but BETWEEN 24 and 25 does not. At least it's consistent! Is > this normal, or am I experiencing some kind of rounding condition? >
Actually, you seem to be experiencing some kind of non-rounding condition. The DATE datatype in Oracle includes date and time. When you specify just a date, you get midnight on that date. Solutions including using TRUNC() on the date values before you put them in REQUEST_DATE, if you'll never care about the time; using TRUNC(REQUEST_DATE) in the WHERE clause; or using BETWEEN specifying a time of 23:59:59 for the second date argument. HTH, Ronald
