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
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment