Results 1 to 7 of 7

Thread: sysdate - 1

  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Red face Unanswered: sysdate - 1

    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!!

  2. #2
    Join Date
    Oct 2003
    Location
    Germany - Stuttgart
    Posts
    14
    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')

  3. #3
    Join Date
    Dec 2003
    Posts
    74

    Re: sysdate - 1

    Originally posted by Naweed
    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!!
    -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

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    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'

  5. #5
    Join Date
    Nov 2003
    Posts
    76
    Thank you.
    How about sutracting 5 minutes from the current time.

  6. #6
    Join Date
    Dec 2003
    Posts
    74
    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

  7. #7
    Join Date
    Nov 2003
    Posts
    76
    Thank you all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •