how do I do a
update sysdate -1
on a table column and still keep the date formatt
'MM/DD/YYYY HH24:MIS'.

this is killing me.
I keep getting the following format after the "update sydate - 1"
DD-MON-YY

it works fine for dual but not any other table.

help!!

Hi,

which oracle version you have? On 9I (9.2.0.4) it works.

Maybe you can try:

to_date(sysdate-1,'DD.MM.RRRR')

-Are you inserting into another date column?
if YES then you just need to change the way your are displaying your date.

SQL> alter session set nls_date_format = 'MM/DD/YYYY HH24:MIS';

Session altered.

SQL> select sysdate-1 from dual;

SYSDATE-1
-------------------
12/21/2003 13:46:42

If you want to convert the date in the format 'MM/DD/YYYY HH24:MIS' to a character field then do the following

SQL> select to_char(sysdate-1,'MM/DD/YYYY HH24:MIS') from dual;

TO_CHAR(SYSDATE-1,'
-------------------
12/21/2003 13:45:34

I hope this helps

Edwin

If you want to make the changes permanent (date format)
the you have to change it in INIT.ORA
NLS_DATE_FORMAT='dd/mm/yyyyHH24:MIS'

Thank you.
How about sutracting 5 minutes from the current time.

select sysdate - 1/24 from dual; = today minus 1 hour

so

select sysdate - (1/24/60)*5 from dual

today / 24 = 1 hour / 60 = 1 minute * 5 give five minutes


regards

edwin

Thank you all