This content has been marked as final. Show 4 replies
Here's one way, using Oracle's date arithmetic:
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.
UPDATE s1_accrual_entry SET add_date = TRUNC (accrual_date) + ( SYSDATE - TRUNC (SYSDATE) ) ;
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: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.
SET add_date = to_date(to_char(SYSDATE,'hh24:mi:ss'),'hh24:mi:ss')
I tried your sql but got this error:
Error starting at line 11 in command:
SET add_date = to_date(trunc(add_date)||to_char(SYSDATE,' hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')
SQL Error: ORA-01830: date format picture ends before converting entire input string
01830. 00000 - "date format picture ends before converting entire input string"