三个Oracle日期函数（十分有用）！！！！！！！！--我还没验证过

来源：互联网发布：flash cs6 for mac 编辑：程序博客网时间：2024/05/21 07:55

函数一：
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;