Friday, September 11, 2009

AGE CALCULATIION

set serveroutput on;
/

CREATE OR REPLACE FUNCTION lr_age (p_bdate IN DATE)
RETURN VARCHAR2 IS
v_years NUMBER;
v_months NUMBER;
v_days NUMBER;
BEGIN
v_years := TRUNC ((MONTHS_BETWEEN (SYSDATE, p_bdate)) / 12);
v_months := TRUNC (MOD ((MONTHS_BETWEEN (SYSDATE, p_bdate)), 12));
v_days :=
TRUNC (MOD ((MOD ((MONTHS_BETWEEN (SYSDATE, p_bdate)), 12) * 30), 30));
RETURN (v_years || ' years ' || v_months || ' months ' || v_days
|| ' days'
);
END;
/
exec dbms_output.put_line(lr_age(to_date('27-01-76','dd-mm-rr')));




set serveroutput on;
/

CREATE OR REPLACE FUNCTION ab_date (p_age DATE)
RETURN VARCHAR2 IS
v_year NUMBER;
v_month NUMBER;
v_day NUMBER;
BEGIN
v_year := TRUNC (MONTHS_BETWEEN (SYSDATE, p_age) / 12);
v_month := TRUNC (MOD ((MONTHS_BETWEEN (SYSDATE, p_age)), 12));
v_day :=
TRUNC (MOD ((MOD ((MONTHS_BETWEEN (SYSDATE, p_age)), 12) * 30), 30));
RETURN (v_day || '-' || v_month || '-' || v_year);
END;
/
exec dbms_output.put_line(ab_date(to_date('24-04-0021','dd-mm-rrrr')));



SELECT TRUNC ((MONTHS_BETWEEN (SYSDATE, :p_bdate)) / 12) YEAR,
TRUNC (MOD ((MONTHS_BETWEEN (SYSDATE, :p_bdate)), 12)) MONTH,
TRUNC (MOD ((MOD ((MONTHS_BETWEEN (SYSDATE, :p_bdate)), 12) * 30), 30)
) days
FROM DUAL

No comments: