Function لحساب العمر

[وليد علاء الدين]

للي عاوز
Function لحساب العمر
انا عملتها معقدة شوية بس شغالة

create or replace function how_old (p_date varchar)
return varchar 
is 
birth_day timestamp(2) := to_date(p_date,'DD-MON-YYYY ');
today timestamp(2) := sysdate;
age varchar2(100);
begin age :='You are ' || to_char
(trunc(
(extract(
day from (today-birth_day))/30.4375)/12)
) 
|| ' years and ' || 
to_char(
trunc(
(mod(
(extract(
day from (today-birth_day))/30.4375)/12,1)*
365.25)/30.4375))
|| ' months and ' || 
to_char(
trunc(mod
((
mod(
(
extract(day from (today-birth_day))/30.4375)/12,1)*
365.25)/30.4375,1)*30.4375))
|| ' days and ' || 
to_char(
to_char(
round(
mod(
mod(
(mod(
(extract(
day from (today-birth_day))/30.4375)/12,1)*
365.25)/30.4375,1)*30.4375,1)*24,5))+  
(extract(
hour from (today-birth_day))) || ' hours and ' ||
to_char
(extract(
minute from (today-birth_day)))) || ' minutes.';
return age;
end;

Select HOW_OLD('12-jan-1982') from dual

Have fun

[وليد علاء الدين]

create or replace function how_old (p_date varchar)
return varchar 
is 
birth_day timestamp(2) := to_date(p_date,'DD-MON-YYYY ');
today timestamp(2) := sysdate;
age varchar2(100);
begin age :='You are ' || to_char
(trunc(
(extract(
day from (today-birth_day))/30.4375)/12)
) 
|| ' years and ' || 
to_char(
trunc(
(mod(
(extract(
day from (today-birth_day))/30.4375)/12,1)*
365.25)/30.4375))
|| ' months and ' || 
to_char(
trunc(mod
((
mod(
(
extract(day from (today-birth_day))/30.4375)/12,1)*
365.25)/30.4375,1)*30.4375))
|| ' days and ' || 
to_char(
to_char(
round(
mod(
mod(
(mod(
(extract(
day from (today-birth_day))/30.4375)/12,1)*
365.25)/30.4375,1)*30.4375,1)*24,5))+  
(extract(
hour from (today-birth_day))) || ' hours and ' ||
to_char
(extract(
minute from (today-birth_day)))) || ' minutes.';
return age;
end;

[وليد علاء الدين]

create or replace function how_old (p_date varchar)
return varchar
is
birth_day timestamp(2) := to_date(p_date,'DD-MON-YYYY ');
today timestamp(2) := sysdate;
age varchar2(100);
begin age :='You are ' || to_char
(trunc(
(extract(
day from (sysdate-birth_day))/30.4375)/12)
)
|| ' years and ' ||
to_char(
trunc(
(mod(
(extract(
day from (sysdate-birth_day))/30.4375)/12,1)*
365.25)/30.4375))
|| ' months and ' ||
to_char(
trunc(mod
((
mod(
(
extract(day from (sysdate-birth_day))/30.4375)/12,1)*
365.25)/30.4375,1)*30.4375))
|| ' days and ' ||
to_char(
to_char(
round(
mod(
mod(
(mod(
(extract(
day from (sysdate-birth_day))/30.4375)/12,1)*
365.25)/30.4375,1)*30.4375,1)*24,5))+
(extract(
hour from (sysdate-birth_day))) || ' hours and ' ||
to_char
(extract(
minute from (sysdate-birth_day)))) || ' minutes.';
return age;
end;