CODEWITHSUNDEEP
sqloracle-database
ilango
ilango

Reputation: 1287

How to update Oracle Timestamp value from the current to a timestamp from the past

I have an Oracle Table called EVENT_TABLE_T. It has a column called LAST_UPDATE_DT. One sample value from this column is: 01-JUL-13 11.20.22.37448900 AM. There are over 700 rows that have this same timestamp value. I would like to update this value to 45 days before this date, using a SQL statement. For example,01-JUL-13 11.20.22.37448900 AM, after my mental arithmetic, should become: 15-May-13 11.00.00...... (exactly 45 days). If this is successful, I would like to apply an update on a different value in LAST_UPDATE_DT that reflects a value that goes back back 46 days.

What I hope to accomplish by asking this question is to be able to learn the basics of Oracle dates and timestamps and apply them to my batch processing work. I would like to be able to run this update sql statement from Oracle SQL Developer and also from inside a Java PreparedStatement.

Thanks in advance for your help.

Upvotes: 12

Views: 106116

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

You can simply subtract a time interval from the timestamp.

UPDATE EVENT_TABLE_T 
SET LAST_UPDATE_DT = last_update_dt - interval '45' day
WHERE LAST_UPDATE_DT = TO_TIMESTAMP('01-JUL-2013 11:20:22:37448900','DD-MON-YYYY HH24: MI:SS:FF')

Upvotes: 26

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

Try something like:

    UPDATE EVENT_TABLE_T 
SET LAST_UPDATE_DT = TO_TIMESTAMP('15-MAY-2013 11:00:00:00','DD-MON-YYYY HH24:MI:SS:FF')
WHERE LAST_UPDATE_DT = TO_TIMESTAMP('01-JUL-2013 11:20:22:37448900','DD-MON-YYYY HH24: MI: SS:FF')

similar for the update you want for going back 46 days.

Check for syntactical error if any

Upvotes: 6

Related Questions