自定义的三个Oracle日期函数 dayadd datediff datepart

函数一：
CREATE OR REPLACE FUNCTION dayadd(
        p_Component         varchar2,
        p_Number        number,
        p_Date                date)
RETURN DATE 
IS
/****************************************************************/
/*      该函数为日期计算函数主要是计算〕                        */
/*    　从当前日期开始经过多少日、季、月、年等后的日期。          */
/*     入参说明：p_Component         时间元件，如年月日季度等等      */
/*                 p_Number        加数, 注意：应该为整数（可正可负）  */
/*                 p_Date                基准时间                            */
/*  注意：其他日期元件，如世纪等等，暂时未考虑                  */
/****************************************************************/

v_Component                  varchar2(10);
v_MiddleNumber          number;
v_ReturnValue_Str         varchar2(20);   --字符串日期格式
v_ReturnValue                 date;           --返回日期

BEGIN
     v_Component := upper(ltrim(rtrim(p_Component)));
     if v_Component in ('Y','YY','YEAR','YYYY')    then      --年情况
        v_ReturnValue := add_months(p_Date,p_Number*12);        
     elsif v_Component in ('M','MM','MONTH','MON') then      --月情况
        v_ReturnValue := add_months(p_Date,p_Number);
     elsif v_Component in ( 'D', 'DD', 'DAY')      then          --日情况
            v_ReturnValue := p_Date + p_Number;
     elsif v_Component in ('H', 'HH', 'HOUR')      then          --时情况
            v_ReturnValue := p_Date + p_Number/24;
     elsif v_Component in ('MI','MINUTE')          then          --分情况
            v_ReturnValue := p_Date + p_Number/1440;
     elsif v_Component in('S', 'SS', 'SECOND')     then          --秒情况
            v_ReturnValue := p_Date + p_Number/86400;
     elsif v_Component in ('Q','QQ','QUARTER')     then          --季度情况
            v_ReturnValue := p_Date + p_Number*3;
        elsif v_Component in ('W','WW','WK','WEEK')    then          --周情况
            v_ReturnValue := p_Date + p_Number*7;
    else 
            v_ReturnValue := to_date('1-1-1','yyyy-mm-dd');
end if;
RETURN v_ReturnValue;
EXCEPTION 
WHEN OTHERS THEN 
RETURN to_date('1-1-1','yyyy-mm-dd');                 --例外处理

END;


函数二：
create or replace function datediff
(p_Component  varchar2 , 
p_Subtranhend  date, 
p_Minuend date) 
RETURN NUMBER 
IS 
/*************************************************************************/
/*      功    能：返回两个日期之间的天、周、月、年等数量。               */
/*      入参说明： p_Component    时间元件，如年月日季度等等             */ 
/*                 p_Subtrahend   减数时间                               */ 
/*                 p_Minuend     被减数时间                              */ 
/*************************************************************************/

v_ReturnValue   number ;      -- 结果数值 
v_Component     varchar2(10); --日期组件中间转换形式，截取空格并且转为大写 
v_YearNum1      number;       --减数年份数 
v_YearNum2      number;       --被减数年份数 
v_MonthNum1     number;       --减数月份数 
v_MonthNum2     number;       --被减数月份数 
v_HourNum1      number;       --减数时数 
v_HourNum2      number;       --被减数时数 
v_MinuteNum1    number;       --减数分钟数 
v_MinuteNum2    number;       --被减数分钟数 
v_SecondNum1    number;       --减数秒钟数 
v_SecondNum2    number;       --减数秒钟数 
v_QuarterValue1 number;       --减数季度数 
v_QuarterValue2 number;       --被减数季度数 
v_WeekNum1      number;       --减数与标准时间周差 
v_WeekNum2      number;       --被减数与标准时间周差 
BEGIN 
    v_Component := upper(ltrim(rtrim(p_Component))); 
   if v_Component in ('Y','YY','YEAR','YYYY') then   --年情况  
                 v_YearNum1  := to_number(to_char(p_Subtranhend,'YYYY')); 
                 v_YearNum2 := to_number(to_char(p_Minuend,'YYYY')) ; 
                 v_ReturnValue := v_YearNum2 - v_YearNum1; 
   elsif v_Component in ('M', 'MM','MONTH', 'MON') then    --月情况 
--请注意，这个部分与oracle内置日期函数MONTH_BETWEEN()不同，忽略了日因素 
--而后者的两个日期如都是所在月的最后一天，才返回整数，否则，返回分数 
--而且这个分数是以31天作为一个月进行计算的结果 
                 v_YearNum1  := to_number(to_char(p_Subtranhend,'YYYY')); 
                 v_YearNum2 := to_number(to_char(p_Minuend,'YYYY')) ; 
                 v_MonthNum1 := to_number(to_char(p_Subtranhend,'MM')); 
                 v_MonthNum2 := to_number(to_char(p_Minuend,'MM')); 
                 v_ReturnValue := (v_YearNum2 - v_YearNum1)*12 + (v_MonthNum2 - v_MonthNum1); 
  elsif v_Component in ( 'D', 'DD', 'DAY') then     --日情况 
--这里与两个日期直接相减的oracle日期算术也不同，只返回整数天数； 
--而后者可以返回一天的几分之几（以小数形式表达） 
         v_ReturnValue := to_date(to_char(p_Minuend,'yyyy-mm-dd'),'YYYY-MM-DD')  
         - to_date(to_char(p_Subtranhend,'yyyy-mm-dd'),'YYYY-MM-DD'); 
  elsif v_Component in ('H', 'HH', 'HOUR') then     --时情况 
--第一步：求出天数 
         v_ReturnValue := (to_date(to_char(p_Minuend,'yyyy-mm-dd'),'YYYY-MM-DD')  
          - to_date(to_char(p_Subtranhend,'yyyy-mm-dd'), 'YYYY-MM-DD')); 
--第二步：求出时数 
                 v_HourNum1 := to_number(to_char(p_Subtranhend,'HH24')); 
                 v_HourNum2 := to_number(to_char(p_Minuend,'HH24')); 
                 v_ReturnValue := v_ReturnValue*24 + (v_HourNum2 - v_HourNum1); 
elsif v_Component in ('MI','MINUTE') then     --分情况 
                 --第一步：求出天数 
                 v_ReturnValue := (to_date(to_char(p_Minuend,'yyyy-mm-dd'),'YYYY-MM-DD')  
                    - to_date(to_char(p_Subtranhend,'yyyy-mm-dd'), 'YYYY-MM-DD')); 
                 --第二步：求出时数 
                 v_HourNum1 := to_number(to_char(p_Subtranhend,'HH24')); 
                 v_HourNum2 := to_number(to_char(p_Minuend,'HH24')); 
                 v_ReturnValue := v_ReturnValue*24 + (v_HourNum2 - v_HourNum1); 
                 --第三步：求出分钟数 
                 v_MinuteNum1 := to_number(to_char(p_Subtranhend,'MI')); 
                 v_MinuteNum2 := to_number(to_char(p_Minuend,'MI')); 
                 v_ReturnValue := v_ReturnValue*60 + (v_MinuteNum2 - v_MinuteNum1); 
elsif v_Component in('S', 'SS', 'SECOND') then     --秒情况 
                 --第一步：求出天数 
                 v_ReturnValue := (to_date(to_char(p_Minuend,'yyyy-mm-dd'),'YYYY-MM-DD')  
                    - to_date(to_char(p_Subtranhend,'yyyy-mm-dd'), 
                      'YYYY-MM-DD')); 
                 --第二步：求出时数 
                 v_HourNum1 := to_number(to_char(p_Subtranhend,'HH24')); 
                 v_HourNum2 := to_number(to_char(p_Minuend,'HH24')); 
                 v_ReturnValue := v_ReturnValue*24 + (v_HourNum2 - v_HourNum1); 
                 --第三步：求出分钟数 
                 v_MinuteNum1 := to_number(to_char(p_Subtranhend,'MI')); 
                 v_MinuteNum2 := to_number(to_char(p_Minuend,'MI')); 
                 v_ReturnValue := v_ReturnValue*60 + (v_MinuteNum2 - v_MinuteNum1); 
                 --第四步：求出秒钟数 
                 v_SecondNum1 := to_number(to_char(p_Subtranhend,'SS')); 
                 v_SecondNum2 := to_number(to_char(p_Minuend,'SS')); 
                 v_ReturnValue := v_ReturnValue*60 + (v_SecondNum2 - v_SecondNum1); 
elsif v_Component in ('Q','QQ','QUARTER') then     --季度情况 
                 v_YearNum1  := to_number(to_char(p_Subtranhend,'YYYY')); 
                 v_YearNum2 := to_number(to_char(p_Minuend,'YYYY')) ; 
                 v_QuarterValue1 := to_number(to_char(p_Subtranhend,'Q')); 
                 v_QuarterValue2 := to_number(to_char(p_Minuend,'Q')); 
                 v_ReturnValue := (v_YearNum2 - v_YearNum1)*4 + (v_QuarterValue2 - v_QuarterValue1); 
elsif v_Component in ('W','WW','WK','WEEK') then    --周情况 
--一周的起始日期应当为星期日 
--关于周差的计算，尝试采用中间日期的方法 
--经查，‘1-1-2’即公元一年1月2日为周日，我们就可以用两个时间分别与其相减求周差 
--两个结果再相减，即可得到正确的数值 
         v_WeekNum1 :=  floor(  (to_date(to_char(p_Subtranhend,'YYYY-MM-DD'),'YYYY-MM-DD') - 
             to_date('1-1-2','YYYY-MM-DD'))/7); 
         v_WeekNum2 :=  floor(  (to_date(to_char(p_Minuend,'YYYY-MM-DD'),'YYYY-MM-DD') - 
             to_date('1-1-2','YYYY-MM-DD'))/7); 
         v_ReturnValue := v_WeekNum2 - v_WeekNum1; 
else  
         v_ReturnValue := -88888; 
end if; 
RETURN v_ReturnValue; 
EXCEPTION  
WHEN OTHERS THEN  
RETURN -99999;--例外处理 
END datediff;


函数三：
create or replace function datepart( 
        p_Component         varchar2,
        p_Date                date)
RETURN NUMBER
IS
/*************************************************************************/
/*       功   能：获取某个日期中的部分时间元件（日、月、年、分、秒、等） */
/*       入参说明：        p_Component 时间元件，如年月日季度等等               */
/*                          p_Date            需要解析的时间                           */
/*************************************************************************/
v_Component   varchar2(10);
v_ReturnValue NUMBER;

BEGIN
v_Component := upper(ltrim(rtrim(p_Component)));

if v_Component in ('Y','YY','YEAR','YYYY') then                 --年情况

        v_ReturnValue := to_number(to_char(p_Date,'YYYY')) ;

elsif v_Component in ('M', 'MM','MONTH', 'MON') then                         --月情况

        v_ReturnValue := to_number(to_char(p_Date,'MM')) ;

elsif v_Component in ( 'D', 'DD', 'DAY') then                                 --日情况

        v_ReturnValue := to_number(to_char(p_Date,'DD')) ;

elsif v_Component in ('H', 'HH', 'HOUR', 'HH24') then                         --时情况

        v_ReturnValue := to_number(to_char(p_Date,'HH24')) ;

elsif v_Component in ('MI','MINUTE') then                                 --分情况

        v_ReturnValue := to_number(to_char(p_Date,'MI')) ;

elsif v_Component in('S', 'SS', 'SECOND') then                                 --秒情况

        v_ReturnValue := to_number(to_char(p_Date,'SS')) ;
        
elsif v_Component in ('Q','QQ','QUARTER') then                                 --季度情况

        v_ReturnValue := to_number(to_char(p_Date,'Q')) ;
        
elsif v_Component in ('W','WW','WK','WEEK') then                         --周几情况（周日为第一天）

        v_ReturnValue := to_number(to_char(p_Date,'D')) ;

elsif v_Component in ('WEEK_NO') then                         -- 第几周情况

        v_ReturnValue := to_number(to_char(p_Date,'IW')) ;

else 
        v_ReturnValue := -88888;
end if;

RETURN v_ReturnValue;

EXCEPTION 
WHEN OTHERS THEN 
RETURN -99999;--例外处理

END datepart;

http://tiantainanhai.blog.163.com/blog/static/3708591120107910261762/

 

[导入]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://www.cnblogs.com/pboy2925/archive/2008/07/03/1248019.html