4 Replies Latest reply: Oct 10, 2012 11:34 AM by 812975 RSS

    Updating Time Portion of date

    Murray Sobol
      I added a new column to an existing table:
      ALTER TABLE s1_accrual_entry
      ADD add_date date DEFAULT sysdate not null
      /

      I need to update this column in 2 parts:
      the DATE portion is derived from an existing column "accrual_date" in the table. this column has the time portion set to zeros.
      the TIME portion needs to be set the current time; i.e the time at which the script was run.
      Here are my 2 scripts:
      UPDATE s1_accrual_entry
      SET add_date = TRUNC(accrual_date)
      /

      UPDATE s1_accrual_entry
      SET add_date = to_date(to_char(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss')
      /

      After running the first script, I got this result in "add_date" 10-OCT-2012 12.00.00 AM which is correct.

      After running the second script, I got this result: 01-OCT-2012 11.48.45 AM
      The TIME portion is correct but the DATE portion changed.

      I dont see anythin im my SQL statement that changed the DATE.
        • 1. Re: Updating Time Portion of date
          812975
          UPDATE s1_accrual_entry
          SET add_date = to_date(trunc(add_date)||to_char(SYSDATE,' hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')
          • 2. Re: Updating Time Portion of date
            Frank Kulash
            Hi,

            Here's one way, using Oracle's date arithmetic:
            UPDATE  s1_accrual_entry
            SET     add_date = TRUNC (accrual_date) + ( SYSDATE
                                           - TRUNC (SYSDATE)
                                       )
            ;
            If dt1 and dt are DATEs, then d1 - d2 is a NUMBER, the number of days (not necessarily an integer, and not necessarily positive) that d1 is after d2. So if the current time is 6:00 AM, then SYSDATE - TRUNC (SYSDATE) is .25, because 6:00 AM is .25 days after midnight.
            Whatever that number is, that's the fraction of a day that you want to add to TRUNC (accrual_date). When you add a DATE dt and a NUMBER n in Oracle, the result is a DATE, n days after dt.
            Murray Sobol wrote:
            ...
            UPDATE s1_accrual_entry
            SET add_date = to_date(to_char(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss')
            Any time you see "to_date(to_char", an alarm should go off in your brain. Oracle provides lots of date manipulation functions as well as date arithmetic. Whatever you need to do to a date can be done to the date, and not to some string representation of the date which then needs to be converted back to a date.
            • 3. Re: Updating Time Portion of date
              Murray Sobol
              I tried your sql but got this error:
              Error starting at line 11 in command:
              UPDATE s1_accrual_entry
              SET add_date = to_date(trunc(add_date)||to_char(SYSDATE,' hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')
              Error report:
              SQL Error: ORA-01830: date format picture ends before converting entire input string
              01830. 00000 - "date format picture ends before converting entire input string"
              *Cause:   
              *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
              • 4. Re: Updating Time Portion of date
                812975
                depends on your nls properties..

                UPDATE s1_accrual_entry
                SET add_date = to_date(to_char(accrual_date, 'dd-mon-yyyy')||to_char(SYSDATE,' hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')

                or

                update s1_accrual_entry
                set     add_date = trunc (accrual_date) + (sysdate-trunc (sysdate));