how to update the date field for this specific condition using oracle query?

Question

I want to update one date column in oracle table for the below scenario...

1. currentYR=Get the Current Year from the SYSDATE (2015)
   
2. currentYR=currentYR-2(2013)
   
3. Need to set date field like 01-JUN-13(Last TWO DIGITS OF currentYR(2013)

Basically i just want to set set date like year=2 years earlier from sysdate and month=june,date=1. and date format is like DD-MON-YY(01-JUN-13)

Please guide me to resolve this problem.

Answer 1

try this:

update yourtable
set yourcolumn = add_months(sysdate, -24)

-24 means 2 years earlier

Answer 2

Based on the comment above I think you would like to set the value of the column to a date where only the last two digits of the year are provided. You can do so by using a Datetime format model reflecting this:

UPDATE <your table> SET <yourcolumn> = TO_DATE('01-JUN-13','DD-MON-YY') WHERE ...;

Answer 3

Finally found the answer. This is the query which i wanted...

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -19 ) FROM DUAL;

QUERY RESULT

01-JUN-13