Oracle 技能强化 Part 8 日期操作

1.SYSDATE 能得到的信息

SELECT hiredate,
to_number(to_char(hiredate, 'hh24')) 时,
to_number(to_char(hiredate, 'mi')) 分,
to_number(to_char(hiredate, 'ss')) 秒,
to_number(to_char(hiredate, 'dd')) 日,
to_number(to_char(hiredate, 'mm')) 月,
to_number(to_char(hiredate, 'yyyy')) 年,
to_number(to_char(hiredate, 'ddd')) 年内第几天,
trunc(hiredate, 'dd') 一天之始,
trunc(hiredate, 'day') 周初,
trunc(hiredate, 'mm') 月初,
last_day(hiredate) 月未,
add_months(trunc(hiredate, 'mm'),1) 下月初,
trunc(hiredate, 'yy') 年初,
to_char(hiredate, 'day') 周几,
to_char(hiredate, 'month') 月份
FROM (SELECT hiredate + 30/24/60/60 + 20/24/60 + 5/24 AS hiredate FROM emp WHERE ROWNUM <=1);

2.确定一年是否为闰年

判断是否为闰年只需要查看二月份的月末是哪一天就好。

SELECT to_char(last_day(add_months(trunc(SYSDATE, 'y'), 1)), 'DD') AS 二月底
  from dual;

3.周的计算

WITH x AS
 (SELECT trunc(SYSDATE, 'YY') + LEVEL - 1 AS 日期
    FROM dual
  CONNECT BY LEVEL <= 8)
SELECT 日期,
       /*返回值 1代表周日,2代表周一....*/
       to_char(日期, 'd') AS d,
       to_char(日期, 'day') AS day,
       /*参数2中1 代表周日,2代表周一....*/
       next_day(日期, 1) AS 下个周日,
       /*ww的算法为每年1月1日为第一周开始，date+6为每一周结尾*/
       to_char(日期, 'ww') AS ww,
       to_char(日期,'iw') as iw
  FROM x;

4.确定一年内属于周内某一天的所有日期

要求返回一年内所有属于周五的日期。用上一节讲到的知识，我们可以用 to_char(dy,'d') = 6 来判断。（Notes: 从星期天开始算第一天的）

WITH x AS
 (SELECT trunc(SYSDATE, 'y') + LEVEL - 1 dy
    FROM dual
  CONNECT BY LEVEL <=
             add_months(trunc(SYSDATE, 'y'), 12) - trunc(SYSDATE, 'y'))
SELECT dy, to_char(dy, 'day') FROM x where to_char(dy, 'd') = 6;

5.确定某月内第一个和最后一个“周内某天” 的日期

返回本月内第一个星期一与最后一个星期一，我们分别找上月末及本月末之前第七天的下一个周一即可。

SELECT next_day(trunc(SYSDATE,'mm')-1,2) 第一个周一,
    next_day(last_day(trunc(SYSDATE,'mm')) -7,2) 最后一个周一
 FROM dual;
 
 
SELECT next_day(trunc(SYSDATE,'mm')-1,3) 第一个周二,
    next_day(last_day(trunc(SYSDATE,'mm')) -7,3) 最后一个周二
 FROM dual;

 

7.创建日历

select month,
       min(decode(weekday, 1, day)) "日",
       min(decode(weekday, 2, day)) "一",
       min(decode(weekday, 3, day)) "二",
       min(decode(weekday, 4, day)) "三",
       min(decode(weekday, 5, day)) "四",
       min(decode(weekday, 6, day)) "五",
       min(decode(weekday, 7, day)) "六"
  from (SELECT a.day,
               to_char(day, 'yyyyMM') month,
               decode(sign(rn - weekday), 1, week + 1, week) week,
               weekday
          FROM (select day,
                       to_char(day, 'mm') month,
                       to_char(day, 'w') week, --周第
                       to_char(day, 'd') weekday, --礼拜几
                       row_number() over(partition by to_char(day, 'mm'), to_char(day, 'w') order by day) rn --安照month和week排个序
                  from (select trunc(sysdate, 'yyyy') + level - 1 day --今年全部年月日
                          from dual
                        connect by rownum <= trunc(sysdate + 365, 'yyyy') -
                                   trunc(sysdate, 'yyyy'))) a)
 GROUP BY month, week
 ORDER BY month, week;

8.列出一年中每个季度的开始日期和结束日期

常用的季度报表中需要先生成季度信息，再与业务关联。为了生成季度信息，我们用add_months 分别增加对应的间隔月数即可。

SELECT LEVEL AS 季度,
       (LEVEL - 1) * 3 + 1 AS 开始月份,
       add_months(trunc(SYSDATE,'y'),(LEVEL - 1) * 3) AS 开始日期,
       add_months(trunc(SYSDATE,'y'),LEVEL * 3) - 1 AS 结束日期
   FROM DUAL
 CONNECT BY LEVEL <= 4;

 

9.确定某个给定季度的开始日期和结束日期

那如果不是当前年度，而某个给出信息比如下面所示的数据，如何生成呢？