SQL>
SQL>
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL>
SQL> select sysdate from dual;
SYSDATE
--------------------
16-JUN-2008 17:29:01
1 row selected.
SQL>
SQL>
SQL> select sysdate - to_dsinterval( '1 00:00:00' ) "US Marines Birthday" from dual;
US Marines Birthday
--------------------
15-JUN-2008 17:29:01
1 row selected.
SQL>
SQL> --