思路:首先求出一年中的第一天和最后一天,然后枚举出一年的日历,最后筛选出星期5的日期即可
-- 求出一年中的第一天和最后一天(这里是第二年的第一天,-1得到最后天)
SQL> select trunc(sysdate,'y') first_day, add_months(trunc(sysdate,'y'),12) last_day from dual;
FIRST_DAY LAST_DAY
---------- ----------
2014-01-01 2015-01-01
-- 求出一年有多少天
SQL> select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') num_days from dual;
NUM_DAYS
----------
365
-- 枚举日期
SQL> select trunc(sysdate, 'y') + level - 1 tdate
from dual
connect by level <= add_months(trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y'))
TDATE
----------
2014-01-01
2014-01-02
2014-01-03
2014-01-04
...
...
2014-12-27
2014-12-28
2014-12-29
2014-12-30
2014-12-31
-- 求星期
SQL> select to_char(sysdate,'day') from dual; TO_CHAR(SYSDATE,'DAY') ------------------------------------ thursday SQL> select to_char(sysdate,'d') from dual; T - 5 这里注意 1 表示星期天 2 表示星期一 3 表示星期二 4 表示星期三 5 表示星期四 6 表示星期五 7 表示星期六
-- 求最后结果
SQL> with x as 2 (select trunc(sysdate, 'y') + level - 1 tdate 3 from dual 4 connect by level <= 5 add_months(trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y')) 6 select tdate, to_char(tdate, 'day') from x where to_char(tdate, 'd') = 6 7 ; TDATE TO_CHAR(TDATE,'DAY') ---------- ------------------------------------ 2014-01-03 friday 2014-01-10 friday 2014-01-17 friday 2014-01-24 friday 2014-01-31 friday 2014-02-07 friday 2014-02-14 friday 2014-02-21 friday 2014-02-28 friday 2014-03-07 friday 2014-03-14 friday 2014-03-21 friday 2014-03-28 friday 2014-04-04 friday 2014-04-11 friday 2014-04-18 friday 2014-04-25 friday 2014-05-02 friday 2014-05-09 friday 2014-05-16 friday 2014-05-23 friday 2014-05-30 friday 2014-06-06 friday 2014-06-13 friday 2014-06-20 friday 2014-06-27 friday 2014-07-04 friday 2014-07-11 friday 2014-07-18 friday 2014-07-25 friday 2014-08-01 friday 2014-08-08 friday 2014-08-15 friday 2014-08-22 friday 2014-08-29 friday 2014-09-05 friday 2014-09-12 friday 2014-09-19 friday 2014-09-26 friday 2014-10-03 friday 2014-10-10 friday 2014-10-17 friday 2014-10-24 friday 2014-10-31 friday 2014-11-07 friday 2014-11-14 friday 2014-11-21 friday 2014-11-28 friday 2014-12-05 friday 2014-12-12 friday 2014-12-19 friday 2014-12-26 friday 52 rows selected.
--求当月的第一个星期一和最后一个星期一
SQL> select next_day(trunc(sysdate,'mm')-1,2),next_day(last_day(trunc(sysdate,'mm'))-7,2) from dual;
NEXT_DAY(T NEXT_DAY(L
---------- ----------
2014-01-06 2014-01-27
转载于:https://blog.51cto.com/5073392/1354610