[导入]Oracle常用函数:DateDiff()

/*
--功能:将字符串转为年月日格式,删除时分秒.
--来源:http://jorkin.reallydo.com/article.asp?id=529
--参数:
      Datechar Varchar2
--Oracle9i测试通过
*/

Create   Or   Replace   Function  CDate(Datechar  In   Varchar2 )  Return  Date  Is
    ReallyDo Date;
Begin
     Select  to_date(to_char(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;

/*
--功能:将字符串转为年月日时分秒格式.
--来源:http://jorkin.reallydo.com/article.asp?id=529
--参数:
      Datechar Varchar2
--Oracle9i测试通过
*/

Create   Or   Replace   Function  CDateTime(Datechar  In   Varchar2 )  Return  Date  Is
    ReallyDo Date;
Begin
     Select  to_date(to_char(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;

/*
--功能:类似MSSQL的日期比较函数
--来源:http://jorkin.reallydo.com/article.asp?id=529
--更新时间:20080721
--参数:
      Datepart  Varchar2 -- 比较年?月?日?
      StartDate Varchar2 -- 起始日期
      EndDate   Varchar2 -- 结束日期
--Oracle9i测试通过
*/

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 ;

文章来源: http://Jorkin.Reallydo.Com/default.asp?id=529

转载于:https://www.cnblogs.com/pboy2925/archive/2008/07/03/1248019.html