Re: Problem comparing dates (SQLPLUS)

Sam Jordan <sjo_at_spin.ch> wrote in message news:38633FAE.82771B50_at_spin.ch...
> Hi
>
> Oracle 8.0.5.0.0. / Linux SuSe 6.0
>
> I somehow think, that the following must be some stupid bug located
> in my brain, but I really can't find the reason why the following
> happens. Here is the SQL statement, which produces results I can't
> understand:
>
> select pu.key pu_key, pu.finishdate, sysdate,
> to_char(last_day(pu.finishdate),'DD.MM.YYYY'),
> to_char(last_day(sysdate),'DD.MM.YYYY') from prod_usr pu where
> last_day(pu.finishdate) < last_day(sysdate);
>
> PU_KEY FINISHDAT SYSDATE TO_CHAR(LA TO_CHAR(LA
> ---------- --------- --------- ---------- ----------
> 328434 01-DEC-99 24-DEC-99 31.12.1999 31.12.1999
> 302244 31-DEC-99 24-DEC-99 31.12.1999 31.12.1999
> 294604 31-DEC-99 24-DEC-99 31.12.1999 31.12.1999
> 295054 31-DEC-99 24-DEC-99 31.12.1999 31.12.1999
> 280586 31-DEC-99 24-DEC-99 31.12.1999 31.12.1999
>
> I would like to list all entries where the month of the column
> 'finishdate' is
> less than the month of 'sysdate', but the statement returns also entries
> where those months are equal. Why does this happen?

The time part of return value of last_day() is the same as the parameter. That's why your condition makes no sense.

You should use

    where pu.finishdate<trunc(sysdate, 'month') Received on Sat Dec 25 1999 - 09:56:33 CST