[Import] common Oracle functions: DateDiff ()

/*
-- Function: Convert the string to the year, month, and day format, and delete the time, minute, and second.
-- Source: http://jorkin.reallydo.com/article.asp? Id = 529
-- Parameter:
Datechar varchar2
-- Oracle9i passed the test
*/

Create or replace function cdate (datechar in varchar2) return date is
Reallydo date;
Begin
Select to_date (to_char (datechar), 'yyyy-MM-DD hh24: MI: ss '),
'Yyyy-MM-DD '),
'Yyyy-MM-DD ')
Into reallydo
From dual;
Return (reallydo );
End cdate;

/*
-- Function: convert a string to the format of year, month, day, hour, minute, and second.
-- Source: http://jorkin.reallydo.com/article.asp? Id = 529
-- Parameter:
Datechar varchar2
-- Oracle9i passed the test
*/

Create or replace function cdatetime (datechar in varchar2) return date is
Reallydo date;
Begin
Select to_date (to_char (datechar), 'yyyy-MM-DD hh24: MI: ss '),
'Yyyy-MM-DD hh24: MI: ss '),
'Yyyy-MM-DD hh24: MI: ss ')
Into reallydo
From dual;
Return (reallydo );
End cdatetime;

/*
-- Function: Date comparison function similar to MSSQL
-- Source: http://jorkin.reallydo.com/article.asp? Id = 529
-- Update Time: 20080721
-- Parameter:
Datepart varchar2 -- year comparison? Month? ?
Startdate varchar2 -- start date
EndDate Varchar2 -- end date
-- Oracle9i passed the test
*/

Create Or Replace Function Datediff
(
Datepart In Varchar2,
StartDate In Varchar2,
EndDate In Varchar2
) Return Number Is
ReallyDo Numeric;
Begin
Select Case Upper (Datepart)
When 'yyyy' Then
Trunc (Extract (Year From CDate (EndDate ))-
Extract (Year From CDate (StartDate )))
When 'M' Then
Datediff ('yyyy', StartDate, EndDate) * 12 +
(Extract (Month From CDate (EndDate ))-
Extract (Month From CDate (StartDate )))
When 'd 'then
Trunc (cdate (enddate)-cdate (startdate ))
When 'H' then
Datediff ('D', startdate, enddate) * 24 +
(To_number (to_char (cdatetime (enddate), 'hh24 '))-
To_number (to_char (cdatetime (startdate), 'hh24 ')))
When 'n' then
Datediff ('D', StartDate, EndDate) * 24*60 +
(To_Number (to_char (CDateTime (EndDate), 'mi '))-
To_Number (to_char (CDateTime (StartDate), 'mi ')))
When's Then
Datediff ('D', StartDate, EndDate) * 24*60*60 +
(To_Number (to_char (CDateTime (EndDate), 'ss '))-
To_Number (to_char (CDateTime (StartDate), 'ss ')))
Else
-29252888
End
Into ReallyDo
From Dual;
Return (ReallyDo );
End Datediff;

Source: http://Jorkin.Reallydo.Com/default.asp? Id = 529