(原)Oracle 获取每周的起始日期

Oracle 获取每周的起始日期

 

代码如下，后面解释：

 

select dateTime begin_day, dateTime+6 end_day, rn+1 rn from
(
  select dateTime, Rownum rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
)

 

 

说明：

dba_objects 表：
只是被借用了一下，而不是用来查询里面内容用的。 
这个表的真正意义在于 —— 它提供了可以罗列多条数据的可能。 由于每年都有 365 or 366 天，
因此，我们需要一个可以罗列出 356 or 366 条记录的表。这个表可以被替换，但前提是该表里至少有
366条数据(为何定在366而不是365条，请自己去体会)。

trunc() 函数 和 to_char()函数：不清楚的去百度查查就行，例子和说明很多

 

注意：

这条语句查询出来后，可能第一个星期并不是从1月1日开始的。
这是正常的现象，因为在没有设置的情况下，Oracle沿用的是默认的星期设置。
如需使用自己定义的规则，诸如 “1月1日为每年第一周，12月31日为每年的最后一周” 这样的规则，
需要额外处理一下1月1日和12月31日这两个日期节点。

 

自定义日期规则的处理：

因为需要，我们需要用到下面这个规则：
1月1日为每年第一周，12月31日为每年的最后一周

提示：
如需使用上面的规则，需要对下面情况进行处理

情况一： 1月1日 = 星期一 && 12月31日 = 星期日 
这是最理想的情况，虽然这种情况我还看到过, 为了严谨，也写到代码中了，代码如下：
select dateTime begin_day, dateTime+6 end_day, rn from
(
  select dateTime, Rownum rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) 

情况二： 1月1日 = 星期一 && 12月31日 <> 星期日 
对于这种情况需要对最后一周做些处理，然后在 union 到拼接即可

1. 获取本年度共有多少个星期
select to_char(add_months(trunc(sysdate, 'yyyy'), 12)-1, 'fmww') into n_totalWeeks from dual;

2. 获取倒数第二周的结束日期
select dateTime+6 end_day into d_endDate from
(
  select dateTime, Rownum rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) where rn = n_totalWeeks-1;

3. 最后一周的SQL代码如下：
select d_endDate+1 begin_day, add_months(trunc(sysdate, 'yyyy'), 12)-1 end_day, rn n_totalWeeks from dual;

4. 拼接SQL:
select dateTime begin_day, dateTime+6 end_day, rn from
(
  select dateTime, Rownum rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) where rn <= n_totalWeeks-1
union
select d_endDate+1 begin_day, add_months(trunc(sysdate, 'yyyy'), 12)-1 end_day, rn n_totalWeeks from dual;

情况三： 1月1日 <> 星期一 && 12月31日 = 星期日
这种情况跟情况二类似，思路和一样，不同之处在于这个需要将1月1日所在的周设置为第一周

1. 获取今年总的星期数：
select to_char(add_months(trunc(sysdate, 'yyyy'), 12)-1, 'fmww') into n_totalWeeks from dual;

2. 获取第二周的开始日期
select dateTime begin_day into d_beginDate from
(
  select dateTime, Rownum rn+1 rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) where rn = 2;

3. 第一周的SQL如下：
select trunc(sysdate, 'yyyy') begin_day, d_beginDate-1 end_day, 1 rn from dual;

4. 拼接完整的SQL:
select trunc(sysdate, 'yyyy') begin_day, d_beginDate-1 end_day, 1 rn from dual
union
select dateTime begin_day, dateTime+6 end_day, rn from
(
  select dateTime, Rownum rn+1 rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
);

情况四： 1月1日 <> 星期一 && 12月31日 <> 星期日
思路跟前面一样，但是需要对第一周和最后一周分别做处理

1. 获取本年的星期总数：
select to_char(add_months(trunc(sysdate, 'yyyy'), 12), 'fmww')-1 into n_totalWeeks from dual;

2. 获取第二周的开始日期：
select dateTime begin_day into d_beginDate from
(
  select dateTime, Rownum rn+1 rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) where rn = 2;

3. 获取倒数第二周的结束日期：
-- 虽然1月1日不是星期一，但oracle默认1月1日为第一周，又因为1月1日不是星期一，所以在查询出来的结果其实是从第二周开始的，
-- 这样在rn不+1的情况下，需要获取倒数第三周的起始日期；
-- rn + 1 的话，就去倒数第二周的起始日期
select dateTime+6 end_day into d_endDate from
(
  select dateTime, Rownum rn+1 rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) where rn = n_totalWeeks-1;

4. 第一周的起始日期SQL:
select trunc(sysdate, 'yyyy') begin_day, d_beginDate-1 end_day, 1 rn from dual;

5. 最后一周的起始日期SQL:
select d_endDate+1 begin_day, add_months(trunc(sysdate, 'yyyy'), 12)-1 end_day, rn n_totalWeeks from dual;

6. 拼装后的SQL:
select trunc(sysdate, 'yyyy') begin_day, d_beginDate-1 end_day, 1 rn from dual
union
select dateTime begin_day, dateTime+6 end_day, rn from
(
  select dateTime, Rownum+1 rn from 
  (
    select trunc(sysdate, 'yyyy')+rownum-1 dateTime from dba_objects 
    where rownum <= (select add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') from dual)
  ) b where to_char(b.dateTime, 'day') = '星期一'
) where rn <= n_totalWeeks-2
unon
select d_endDate+1 begin_day, add_months(trunc(sysdate, 'yyyy'), 12)-1 end_day, rn n_totalWeeks from dual;

 

注意： 代码中 n_totalWeeks、d_beginDate、d_endDate 均为自定义变量。