Saturday, January 19, 2008

Oracle 10g: MONTHS_BETWEEN

/*
I had a problem in comparing 2 dates in one of my execution task.
It seems like I couldn't find DATEDIFF function in Oracle 10g reference.
so the most I could do is to use MONTHS_BETWEEN to calculate
the date difference between those 2 required dates.

Tricky part here is that..it only compares MONTHS.
If i have to compare for a range of 3 years (say for example),
then I have to times 12 -->[(3 * 12)]
*/

UPDATE wf_trnworkflow
SET ARCHIVEFLAG = 'NonActive'
WHERE (
MONTHS_BETWEEN(to_date(SYSDATE, 'DD-MON-YY HH:MI:SS'),to_date(CREATEDDATE, 'DD-MON-YY HH:MI:SS')) = (3 * 12)
OR
MONTHS_BETWEEN(to_date(SYSDATE, 'DD-MON-YY HH:MI:SS'),to_date(CREATEDDATE, 'DD-MON-YY HH:MI:SS')) > (3 * 12)
);

No comments: