Cliff Nadler wrote:
Try the following instead:
trunc(SHIPDATE) = trunc(SYSDATE)-21
Oracle's date fields (and especially SYSDATE) are date + time. The
TRUNC will trucate the value to the date only (actually midnight) so
they can compare correctly.
It would be curious if trunc works, but there is nothing
wrong with the way Joseph was doing it -- both where clauses
should yield exactly the same results. For example:
SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY') ;
SHIPDATE
-------------------
2006-05-04 12:26:59
SQL> select * from x_ship
where trunc(shipdate) = trunc(sysdate-21);
SHIPDATE
-------------------
2006-05-04 12:26:59
I don't see any reason for your problem. You should turn on
tracing and run the query from sqlplus and from DBI and see
what the db is actually executing in each case.
I would add however that using the trunc is, IMO, preferred,
because the 1) the to_char character conversion serves no
purpose, and 2) it muddies the intention. You *want* to
compare *dates*, so you should compare dates.
Mark
