Oracle经典实例——日期运算

1.年月日加减法

在给定日期的基础上加上或减去若干天、月或年，以入职时间hiredate为例：

  select hiredate - 5 as hd_minus_5D,
         hiredate + 5 as hd_plus_5D,
         add_months(hiredate, -5) as hd_minus_5M,
         add_months(hiredate, 5) as hd_plus_5M,
         add_months(hiredate, -5 * 12) as hd_minus_5Y,
         add_months(hiredate, 5 * 12) as hd_plus_5Y
  from emp
 where deptno = 10;

oracle执行日期运算时，对于date类型的运算，整数值代表天数，加减月或年时使用ADD_MONTHS函数。

2.计算两个日期之间的天数

 select to_date('20200306', 'yyyy-mm-dd') -
       to_date('20190306', 'yyyy-mm-dd')
  from dual;

3.计算两个日期之间相差的月份和年份

使用MONTH_BETWEEN函数找出两个日期之间相差多少个月（除以12得到相差的年份)：

 select months_between(max_date,min_date),
       months_between(max_date,min_date)/12
  from dual;

4.计算两个时间之间相差的小时数、分钟数、秒数

使用减法计算两个时间相差多少天，再进行时间单位换算：

 select dy*24 hr,dy*24*60 min,dy*24*60*60 sec from 
       (select enddate-startdate dy from table_name));

5.计算一年中有几个星期几

 with x as (
  select level lvl from dual
  connect by level <=(add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))
)
select to_char(trunc(sysdate,'y')+lvl-1,'DAY') weekday,count(*) day_num from x 
group by to_char(trunc(sysdate,'y')+lvl-1,'DAY');

首先使用WITH构造一个临时表x,该表中使用level和connect by构造一个1-365或1-366的序列，
接下来的语句中使用TO_CHAR函数将日期转换为星期并对其进行分组统计

6.计算当前记录和下一条记录之间的日期差

使用窗口函数LEAD OVER访问相当于当前date的下一个date：

select now_date, next_date, next_date - now_date diff
  from (select now_date, lead(now_date) over(order by now_date) next_date
          from table_name);

遇到重复日期需要做别的处理

---

整理自《SQL经典实例》