Re: Problem comparing dates (SQLPLUS)

Hi Sam
U must be feeling strange or may not be understanding how oracle behaves,
But I have worked with all the combinations of last_day and found out that this query would work .

select last_day(to_date('24/DEC/1999','dd/mm/yyyy')), last_day(sysdate)
from dual
where
last_day(to_date('24/DEC/1999','dd/mm/yyyy')) < last_day
(TO_date(sysdate,'dd/mm/yyyy'))

I guess last_Day function requires valid date, that's why the where clause is failing.
Why don't u try the same select statement as written by u, but add addition two select parameters(select last_day
(pu.finishdate), last_day(sysdate),......other parameters)
i.e
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'), last_day(pu.finishdate), last_day(sysdate), from
prod_usr pu
where
last_day(pu.finishdate) < last_day(sysdate);

U might get the answer how oracle is bahaving, after getting the result.

I hope it will solve ur query
Regards,
Sudhakar.

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?

Thanks for every comment.

bye --
Sam Jordan

Reply to this message

• Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful