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)
);
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)
);
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment