Menu
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.
662
select to_char(sysdate, 'YYYY-MM-DD') from dual; To get this format by default, set it in your session's NLS_DATE_FORMAT parameter: alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD'; You can also set the NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT .
If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '. 000000' on the end of this conversion.
SYSTIMESTAMP returns current timestamp on database server, while current_timestamp returns current timestamp on client machine. So if your database server is in New York and client box is in California, SYSTIMESTAMP will be 3 hours ahead of CURRENT_TIMESTAMP.
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
