Date arithmetic can be convoluted in Oracle. A customer wanted to know
how to find all the records entered into a table since midnight. In
order to do this, you can convert the column containing the timestamp
to character with the format in decreasing chronological order; year,
month, day, hour, minute, second. You can also truncate a date field to
drop the time and leave just the date. Now find the sysdate and subtract
one day. Any record created since midnight will be selected.
unix> date
Fri Sep 1 13:32:00 EDT 2000
unix> sqlplus user/password@oracle_sid
SQL> alter session set nls_date_format=’YYYY/MM/DD HH24:MI:SS’ ;
SQL> select UPDATE_TIMESTAMP from purchase_log where
SQL> to_char (UPDATE_TIMESTAMP, ‘YYYY/MM/DD HH24:MI:SS’) >
SQL> (select to_char (trunc (sysdate) – 1, ‘YYYY/MM/DD HH24:MI:SS’)
from dual) ;
UPDATE_TIMESTAMP
——————-
2000/09/01 00:12:51