oracle日期相减年龄,自定义年龄函数 ★Oracle版本★（规则二）

1.DateAdd

CREATE OR REPLACE Function DateAdd

(

Datepart In Varchar2,

c In number,

curDate In Date

) Return Date Is

ReallyDo Date;

Begin

Select Case Upper(Datepart)

When 'D' Then

curDate+c

Else

sysdate

End

Into ReallyDo

From Dual;

Return(ReallyDo);

End DateAdd;

-------------------------------------

2.DateDiff

CREATE OR REPLACE Function Datediff

(

Datepart In Varchar2,

StartDate In Date,

EndDate In Date

) Return Number Is

ReallyDo Numeric;

Begin

Select Case Upper(Datepart)

When 'YYYY' Then

Trunc(Extract(Year From EndDate) -

Extract(Year From StartDate))

When 'M' Then

Datediff('YYYY', StartDate, EndDate) * 12 +

(Extract(Month From EndDate) -

Extract(Month From StartDate))

When 'D' Then

Trunc(EndDate - StartDate)

When 'H' Then

Datediff('D', StartDate, EndDate) * 24 +

(to_Number(to_char(EndDate, 'HH24')) -

to_Number(to_char(StartDate, 'HH24')))

When 'N' Then

Datediff('D', StartDate, EndDate) * 24 * 60 +

(to_Number(to_char(EndDate, 'MI')) -

to_Number(to_char(StartDate, 'MI')))

When 'S' Then

Datediff('D', StartDate, EndDate) * 24 * 60 * 60 +

(to_Number(to_char(EndDate, 'SS')) -

to_Number(to_char(StartDate, 'SS')))

Else

-29252888

End

Into ReallyDo

From Dual;

Return(ReallyDo);

End Datediff;

------------------------------

3.DateDepart

CREATE OR REPLACE Function Datepart

(

Datepart In Varchar2,

curDate In Date

) Return number Is

ReallyDo numeric;

Begin

Select Case Upper(Datepart)

When 'Y' Then

to_number(to_char(curDate, 'YYYY'), '999999')

When 'MM' Then

to_number(to_char(curDate, 'MM'), '999999')

When 'D' Then

to_number(to_char(curDate, 'DD'), '999999')

When 'H' Then

to_number(to_char(curDate, 'HH24'), '999999')

When 'MI' Then

to_number(to_char(curDate, 'MI'), '999999')

Else

-29252888

End

Into ReallyDo

From Dual;

Return(ReallyDo);

End Datepart;

------------------------------------

4.自定义函数实现uf_getAge

create or replace function uf_GetAge(birthDate in date)

RETURN VARCHAR2 IS

RESULT VARCHAR2(50);

curNow date;

y number;

m number;

d number;

h number;

mi number;

IsRunNian number := 0;

lastMonthDayCount number;

nowmonth number;

nowday number;

showMonth number := 3; --显示月的最大年龄 <3

showDay number := 1; --显示天的最大月 <1

showHour number := 3; --显示小时的最大天 <3

showMin number := 3; --显示分钟的最大小时 <3

allHours number;--总小时数

allMins number;--总分钟数

newBirthDate date;

begin

select sysdate into curNow from dual;

if to_char(birthDate, 'HH24:MI:SS') > to_char(curNow, 'HH24:MI:SS') then

select dateadd('D',1,birthDate) into newBirthDate from dual;

else

newBirthDate := birthDate;

end if;

select to_char(curNow, 'YYYY') - to_char(newBirthDate, 'YYYY') into y from dual;

select to_char(curNow, 'MM') - to_char(add_months(newBirthDate, y*12), 'MM') into m from dual;

select to_char(curNow, 'DD') - to_char(add_months(newBirthDate, y*12+m), 'DD') into d from dual;

-- return d

-- select y,m, d

if ((mod(datepart('Y', curNow), 4) =0 and mod(datepart('Y', curNow),100)<>0) or ( mod(datepart('Y', curNow),400)=0)) then

IsRunNian:=1;

end if;

select datepart('MM', curNow) into nowmonth from dual;

select datepart('D', curNow) into nowday from dual;

nowmonth := nowmonth - 1;

if nowmonth in(1,3,5,7,8,10,12) then--进入xx月xx天判断

lastMonthDayCount := 31;

elsif nowmonth in(4,6,9,11) then

lastMonthDayCount := 30;

elsif nowmonth=2 then

if IsRunNian=0 then

lastMonthDayCount:=28;

elsif IsRunNian=1 then

lastMonthDayCount:=29;

end if;

end if;

-- select lastMonthDayCount

if (d < 0) then

d := lastMonthDayCount + d;

m := m -1;

end if;

if (m < 0) then

m := 12 + m;

y := y -1;

end if;

if(y >=0 and m >= 0 and d >= 0) then

if y > 0 then

result := to_char(y) || '岁';

end if;

if (y >= showMonth) then

RETURN(result);

end if;

if m > 0 then

result := result || to_char(m) || '月';

end if;

if (m >=showDay) then

RETURN(result);

end if;

if d > 0 then

result := result || to_char(d) || '天';

end if;

if (d >=showHour) then

RETURN(result);

end if;

SELECT trunc(to_number(sysdate - (TO_DATE(to_char(curNow, 'YYYY-MM-DD ') ||to_char(newBirthDate, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:mi:ss')-1))*24) into allHours from dual;

select mod(allHours, 24) into h from dual;

if h > 0 then

result := result || h||'小时';

end if;

if (h >=showMin) then

RETURN(result);

end if;

SELECT trunc(to_number(sysdate - (TO_DATE(to_char(curNow, 'YYYY-MM-DD ') ||to_char(newBirthDate, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:mi:ss')-1))*24*60) into allMins from dual;

select mod(allMins, 60) into mi from dual;

if mi > 0 then

result := result || mi||'分';

else

result := result;

end if;

RETURN(result);

end if;

return '0';

end uf_getage;