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;
