oracle中怎么判断为周五,求一年中所有星期五的日期

思路：首先求出一年中的第一天和最后一天，然后枚举出一年的日历，最后筛选出星期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