Effective ways of doing date arithmatic

Hi ,

I have a requirement to do some date arithmatic and looking for some inputs on doing this in an effective way...basically the birthdate of a person is the input...from that we have to find the date on which his 65th birthday comes-say X...now if X is on 1st of a month then our 'special date' -Say Y- is a month prior to this X. But if X is not on 1st of a month, then Y is to be 1st of the month(like if X is 24- March-2040 then Y is 01-March-2040 but if X is 01-March-2040 then Y is 01-February-2040). The table contains a few ten thousands of records. So to calculate this date I used the following table and madeup this query:

table emp1 (same structure as the famous emp table of the scott schema) + birth_date(date) --an extra field added to this table.

and the query I wrote to find the special date is:

select decode(to_number(to_char(add_months(birth_date,780),'DD')), 1,ADD_MONTHS(birth_date,799),
trunc(add_months(birth_date,780)) ) from emp1

Now this is working but is this the best way to do it or is there a better way....another question, this special date has to be updated in another table (say emp2 for example- some date field of emp2) then what would be an efficient way to use pl/sql to do this?

With thanks,
Nirav Received on Wed Mar 21 2007 - 14:44:16 CDT