Oracle日期函数dayadd/datediff/datepart

于 2010-12-02 14:36:00 发布
阅读量8.4k 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： Oracle 文章标签： oracle date function qq
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/itsoft2006/article/details/6050094
本文介绍三个日期操作函数：dayadd用于计算指定日期增量后的日期；datediff用于计算两个日期间的差值；datepart用于提取日期中的特定组成部分。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

函数一：

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;

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值