| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Effective ways of doing date arithmatic
On Mar 21, 3:44 pm, "nirav" <shiva..._at_gmail.com> wrote:
> 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
The setup:
CREATE TABLE T4 (BIRTH_DATE DATE);
INSERT INTO
T4
SELECT
TRUNC(SYSDATE)+ROWNUM-15
FROM
DUAL
CONNECT BY
LEVEL<=100;
COMMIT;
What if, we fast forward 65 years (65*12) and then back up one day?
If we then truncate the date back to just the month, we may have found
a way to simply solve the problem. Watch out for people born on Feb.
29.:
SELECT
BIRTH_DATE,
ADD_MONTHS(BIRTH_DATE,65*12) DATE_AT_65,
TRUNC(ADD_MONTHS(BIRTH_DATE,65*12)-1,'MM') MONTH_AT_65
FROM
T4
ORDER BY
BIRTH_DATE;
BIRTH_DAT DATE_AT_6 MONTH_AT_
--------- --------- --------- 07-MAR-07 07-MAR-72 01-MAR-72 08-MAR-07 08-MAR-72 01-MAR-72 09-MAR-07 09-MAR-72 01-MAR-72 10-MAR-07 10-MAR-72 01-MAR-72 11-MAR-07 11-MAR-72 01-MAR-72 12-MAR-07 12-MAR-72 01-MAR-72 13-MAR-07 13-MAR-72 01-MAR-72 14-MAR-07 14-MAR-72 01-MAR-72
24-MAR-07 24-MAR-72 01-MAR-72 25-MAR-07 25-MAR-72 01-MAR-72 26-MAR-07 26-MAR-72 01-MAR-72 27-MAR-07 27-MAR-72 01-MAR-72 28-MAR-07 28-MAR-72 01-MAR-72 29-MAR-07 29-MAR-72 01-MAR-72 30-MAR-07 30-MAR-72 01-MAR-72 31-MAR-07 31-MAR-72 01-MAR-72 01-APR-07 01-APR-72 01-MAR-72 02-APR-07 02-APR-72 01-APR-72 03-APR-07 03-APR-72 01-APR-72
26-APR-07 26-APR-72 01-APR-72 27-APR-07 27-APR-72 01-APR-72 28-APR-07 28-APR-72 01-APR-72 29-APR-07 29-APR-72 01-APR-72 30-APR-07 30-APR-72 01-APR-72 01-MAY-07 01-MAY-72 01-APR-72 02-MAY-07 02-MAY-72 01-MAY-72 03-MAY-07 03-MAY-72 01-MAY-72 04-MAY-07 04-MAY-72 01-MAY-72 05-MAY-07 05-MAY-72 01-MAY-72 06-MAY-07 06-MAY-72 01-MAY-72
24-MAY-07 24-MAY-72 01-MAY-72 25-MAY-07 25-MAY-72 01-MAY-72 26-MAY-07 26-MAY-72 01-MAY-72 27-MAY-07 27-MAY-72 01-MAY-72 28-MAY-07 28-MAY-72 01-MAY-72 29-MAY-07 29-MAY-72 01-MAY-72 30-MAY-07 30-MAY-72 01-MAY-72 31-MAY-07 31-MAY-72 01-MAY-72 01-JUN-07 01-JUN-72 01-MAY-72 02-JUN-07 02-JUN-72 01-JUN-72 03-JUN-07 03-JUN-72 01-JUN-72 04-JUN-07 04-JUN-72 01-JUN-72
 |
 |