sql cookbook笔记 第九章 日期操作

91 确定一年是否为闰年
问题：确定当前年是否为闰年。
只检查2月的最后一天，如果它为29，则当前年就是闰年。
使用函数LAST_DAY,可计算出2月份的最后一天：
select to_char(last_day(add_months(trunc(sysdate, 'y'), 1)), 'DD')
  from dual
92 确定一年内的天数
问题：计算当前年天数
解决方案：当前年的天数等于第二年的第一天与当前年第一天（以日为单位）之差。针对每个系统的解决方案，以下步骤相同：
1.找到当前年的第一天。
2.给该日期加1年（即可得到第二年的第一天）。
3.从第二步的结果中减去当前年。
select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual;
93 从日期中提取时间的各部分
问题：把当前日期分为6部分：日，月，年，秒，分时，而且以数字方式返回结果。
select to_number(to_char(sysdate, 'hh24')) hour,
       to_number(to_char(sysdate, 'mi')) min,
       to_number(to_char(sysdate, 'ss')) sec,
       to_number(to_char(sysdate, 'dd')) day,
       to_number(to_char(sysdate, 'mm')) mth,
       to_number(to_char(sysdate, 'yyyy')) year
  from dual
94 确定某个月的第一天和最后一天
问题：确定当前月的第一天和最后一天
select trunc(sysdate,'mm') firstday,last_day(sysdate) lastday from dual;
95 确定一年内属于周内某一天的所有日期
找出一年内属于周内某一天的所有日期。例如，列出当年中的所有星期五的日期。
with x as ( select trunc(sysdate,'y')+level-1 dy from t1 connect by level <= add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y'))
select dy from x where to_char(dy,'dy') ='星期五'
96 确定某月内第一个和最后一个“周内某天”的日期
问题：例如，找出当前月的第一个星期一及最后一个星期一的日期
with x as (select trunc(sysdate,'mm')+level-1 dy from dual connect by level <= add_months(trunc(sysdate,'mm'),1)-trunc(sysdate,'mm'))
select min(dy),max(dy) from x where to_char(dy,'dy')='星期一'
select next_day(trunc(sysdate, 'mm') - 1, '星期一') first_monday,
       next_day(last_day(trunc(sysdate, 'mm')) - 7, '星期一') last_monday
  from dual
97 创建日历
问题：为当前月创建一个日历。
with x as (
     select　* from (
              select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
                     to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
                     to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
                     to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
                     to_char(sysdate,'mm') mth
                     from dual
                     connect by level <=31
     ) where curr_mth=mth
)
select max(case dw when 2 then dm end) Mo,
       max(case dw when 3 then dm end) Tu,
       max(case dw when 4 then dm end) We,
       max(case dw when 5 then dm end) Th,
       max(case dw when 6 then dm end) Fr,
       max(case dw when 7 then dm end) Sa,
       max(case dw when 1 then dm end) Su
       from x group by wk order by wk
98 列出一年中每个季度的开始日期和结束日期
问题：返回一年中每个季度的开始日期和结束日期。
select rownum qtr,
       add_months(trunc(sysdate, 'y'), (rownum - 1) * 3) q_start,
       add_months(trunc(sysdate, 'y'), rownum * 3) - 1 q_end
  from emp
 where rownum <= 4
99 确定某个给定季度的开始日期和结束日期
问题：对于YYYYQ格式（其中4位年，一位季度）的年和季度信息，返回该季度的开始日期和结束日期。
select add_months(q_end, -2) q_start, last_day(q_end) q_end
  from (select to_date(substr(yrq, 1, 4) || mod(yrq, 10) * 3, 'yyyymm') q_end
          from (select 20051 yrq
                  from dual
                union all
                select 20052 yrq
                  from dual
                union all
                select 20053 yrq
                  from dual
                union all
                select 20054 yrq from dual) x) y
910 填充丢失的日期
问题：为给定范围内的每个日期（每个月，周或年）生成一行信息。
with x as (
     select add_months(start_date,level-1) start_date
     from (
          select min(trunc(qsdate,'y')) start_date,
          add_months(max(trunc(qsdate,'y')),12) end_date
          from emp where empid='1'
     )
     connect by level<= months_between(end_date,start_date)
)
select x.start_date MTH,count(e.qsdate) num_hired from x,emp e
where x.start_date =trunc(e.qsdate(+),'mm') group by x.start_date order by 1
第二种方式
with x as (
     select add_months(start_date,level-1) start_date
     from(
            select min(trunc(qsdate,'y')) start_date,
            add_months(max(trunc(qsdate,'y')),12) end_date from emp
     ) connect by level <= months_between(end_date,start_date)
)
select x.start_date MTH,count(e.qsdate) num_hired from x left join emp e on (x.start_date=trunc(e.qsdate,'mm')) group by x.start_date order by 1
911 按照给定的时间单位进行查找
问题：查找与给定月份，星期几或其他时间单位相匹配的日期。
找2月12月周二入职的员工
select empname
  from emp
 where rtrim(to_char(sysdate, 'month')) in ('2月', '12月')
    or rtrim(to_char(sysdate, 'day')) = '星期二'
912 使用日期的特殊部分比较记录
问题：查找聘用日期月份和周内日期都相同的员工，比如1988年3月10日星期一 与 2001年3月2日星期一 同月同星期
select a.empname || ' was hired on the same month and weekday as ' ||
       b.empname as msg
  from emp a, emp b
 where to_char(a.qsdate, 'DMON') = to_char(b.qsdate, 'DMON')
   AND a.Emps < b.Emps
 order by a.empname
没弄明白
913 识别重叠的日期范围
问题：查找员工在老工程结束之前就开始新工程的所有实例。
select a.empid,
       a.empname,
       'project' || b.proj_id || 'overlaps project' || a.proj_id as msg
  from emp a, emp b
 where a.empid = b.empid
   and b.qsdate >= a.qsdate
   and b.qsdate <= a.jsdate
   and a.proj_id != b.proj_id