1 Create Or Replace Function CDate(Datechar In Varchar2) Return Date Is 2 ReallyDo Date; 3 Begin 4 Select to_date(to_char(to_date(to_char(Datechar), 'YYYY-MM-DD HH24:MI:SS'), 5 'YYYY-MM-DD'), 6 'YYYY-MM-DD') 7 Into ReallyDo 8 From Dual; 9 Return(ReallyDo); 10 End CDate; 11 12 13 14 Create Or Replace Function CDateTime(Datechar In Varchar2) Return Date Is 15 ReallyDo Date; 16 Begin 17 Select to_date(to_char(to_date(to_char(Datechar), 'YYYY-MM-DD HH24:MI:SS'), 18 'YYYY-MM-DD HH24:MI:SS'), 19 'YYYY-MM-DD HH24:MI:SS') 20 Into ReallyDo 21 From Dual; 22 Return(ReallyDo); 23 End CDateTime; 24 25 26 27 Create Or Replace Function Datediff 28 ( 29 Datepart In Varchar2, 30 StartDate In Varchar2, 31 EndDate In Varchar2 32 ) Return Number Is 33 ReallyDo Numeric; 34 Begin 35 Select Case Upper(Datepart) 36 When 'YYYY' Then 37 Trunc(Extract(Year From CDate(EndDate)) - 38 Extract(Year From CDate(StartDate))) 39 When 'M' Then 40 Datediff('YYYY', StartDate, EndDate) * 12 + 41 (Extract(Month From CDate(EndDate)) - 42 Extract(Month From CDate(StartDate))) 43 When 'D' Then 44 Trunc(CDate(EndDate) - CDate(StartDate)) 45 When 'H' Then 46 Datediff('D', StartDate, EndDate) * 24 + 47 (to_Number(to_char(CDateTime(EndDate), 'HH24')) - 48 to_Number(to_char(CDateTime(StartDate), 'HH24'))) 49 When 'N' Then 50 Datediff('D', StartDate, EndDate) * 24 * 60 + 51 (to_Number(to_char(CDateTime(EndDate), 'MI')) - 52 to_Number(to_char(CDateTime(StartDate), 'MI'))) 53 When 'S' Then 54 Datediff('D', StartDate, EndDate) * 24 * 60 * 60 + 55 (to_Number(to_char(CDateTime(EndDate), 'SS')) - 56 to_Number(to_char(CDateTime(StartDate), 'SS'))) 57 Else 58 -29252888 59 End 60 Into ReallyDo 61 From Dual; 62 Return(ReallyDo); 63 End Datediff;