SQL Cookbook（2010年11月11日）

第8章、日期运算

 

   1、加减日、月、年

 

   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

 

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

 

   select ward_hd, allen_hd, ward_hd - allen_hd from
     (select hiredate as ward_hd from emp where ename='WARD') x,
     (select hiredate as allen_hd from emp where ename='ALLEN') y

 

   3、确定两个日期之间的工作日数目

 

   表EMP中，计算BLAKE和JONES的hiredate（聘用日期）之间的工作日数（除去星期六、星期天）

 

   建立索引表

 

create table t500(
      id number(3)
   )

    

   插入索引数据

 

declare 
 v_index number(3);
begin
 for v_index in 1..500 loop
     insert into t500 values(v_index);
 end loop;
end;

    

   查询语句

 

select 
  sum(case when to_char(jones_hiredate+t500.id-1, 'DY') in ('星期六', '星期日') then 0 else 1 end) as days
  from (select 
  max(case when ename='BLAKE' then hiredate end) as blake_hiredate,
  max(case when ename='JONES' then hiredate end)  as jones_hiredate
  from emp where ename in ('BLAKE', 'JONES')
) x, t500 where t500.id <= blake_hiredate - jones_hiredate + 1

   

   4、确定两个日期之间的月份数或年数

 

   例如：EMP表中，求第一个员工和最后一个员工之间相差的月份数，以及这些月折合的年数

 

  

select months_between(max_hiredate, min_hiredate),
  months_between(max_hiredate, min_hiredate)/12
from (select min(hiredate) min_hiredate, max(hiredate) max_hiredate from emp)

 

   5、确定两个日期之间的秒、分、小时数

 

   例如：EMP表中，求ALLEN和WARD的hiredate（聘用日期）之间相差的时间，分别用秒、分、小时表示

  

select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec
from (select (max(case when ename='WARD' then hiredate end)-max(case when ename='ALLEN' then hiredate end)) as dy
from emp)

 

   6、计算一年中周内各日期的次数 

   

select to_char(trunc(sysdate, 'y')+rownum-1, 'DY'), count(*)
   from t500 where rownum <=
 add_months(trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y')
   group by to_char(trunc(sysdate, 'y')+rownum-1, 'DY')

 

   7、确定当前记录和下一条记录之间相差的天数

  

select ename, hiredate, next_hd, next_hd-hiredate diff
from (
  select deptno, ename, hiredate, lead(hiredate) over(order by hiredate) next_hd from emp
) where deptno=10

 

第9章、日期操作

 

   1、确定一年是否为闰年

 

   此处采用了最简单的方案，检查2月最后一天，如果是29，则当年就为闰年，即此处的关键是得到2月的最后一天

 

select to_char(last_day(add_months(trunc(sysdate, 'y'), 1)),'DD') from dual

 

   2、确定一年内的天数

  

select add_months(trunc(sysdate, 'y'), 12)-trunc(sysdate, 'y') from dual

 

   3、从日期中提取时间的各个部分

 

select to_char(sysdate, 'hh24') hour,
   to_char(sysdate, 'mi') min, to_char(sysdate, 'ss') sec,
   to_char(sysdate, 'dd') day, to_char(sysdate, 'mm') month,
   to_char(sysdate, 'yyyy') year 
   from dual

 

   4、确定某个月的第一天和最后一天

     

select trunc(sysdate, 'mm') firstday,
 last_day(sysdate) lastday  
 from dual