Oracle is not a very friendly enviornment to deal with date types. I always find myself googling around to find a proper method to deal with date arithmetics. The below is a method I came across that helps adding and subtracting years, months, and days from a date type.
Enjoy!
update employee set SERVICE_DATE = add_months(SERVICE_DATE, -1200)
where employee_id in (select e.EMPLOYEE_ID
from INTERFACE_EXTRACT et, danaher.employee e
where et.NUMBER = e.EMPLOYEE_ID
and et.HIRE_DATE != e.SERVICE_DATE
and to_number(to_char(e.SERVICE_DATE, 'YYYY'), '9999') > 2010)
The above query for example sets the SERVICE_DATE column to be 1200 months earlier. The return value of the add_months function is of type date.
Enjoy!
update employee set SERVICE_DATE = add_months(SERVICE_DATE, -1200)
where employee_id in (select e.EMPLOYEE_ID
from INTERFACE_EXTRACT et, danaher.employee e
where et.NUMBER = e.EMPLOYEE_ID
and et.HIRE_DATE != e.SERVICE_DATE
and to_number(to_char(e.SERVICE_DATE, 'YYYY'), '9999') > 2010)
The above query for example sets the SERVICE_DATE column to be 1200 months earlier. The return value of the add_months function is of type date.
Comments