Hedstrom, Charlie wrote:
If the intent of the query is to find all entries made anytime during
the day 21 days ago, you will need the trunk function.
Nope.
SQL> select * from x_ship
where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')
SHIPDATE
-------------------
2006-05-04 00:01:29
2006-05-04 00:01:30
2006-05-04 00:01:31
2006-05-04 00:01:32
2006-05-04 00:01:33
2006-05-04 00:01:34
...
This query should clarify for you:
select shipdate,
to_char(shipdate, 'MMDDYY') as CHR_SHIP_DATE,
to_char(sysdate-21, 'MMDDYY') as CHR_SYSDATE_MINUS_21
from
x_ship where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')
SHIPDATE CHR_SHIP_DATE CHR_SYSDATE_MINUS_21
------------------- ------------- --------------------
2006-05-04 00:01:29 050406 050406
2006-05-04 00:01:30 050406 050406
2006-05-04 00:01:31 050406 050406
2006-05-04 00:01:32 050406 050406
2006-05-04 00:01:33 050406 050406
2006-05-04 00:01:34 050406 050406
2006-05-04 00:01:35 050406 050406
2006-05-04 00:01:36 050406 050406
Mark
