Question

oracle中 我给出一个周数 用什么函数可以取出这周的开始日期和结束日期？

比如 第32周 我想知道32周的 这周的开始时间和结束时间。

Answer 1

select SUNDAY,SATURDAY from
(select
sunday.the_week,decode(sign(sunday.the_day-saturday.the_day),-1,sunday.the_day,sunday.the_day-7)
sunday,saturday.the_day saturday from
(select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day from (select
trunc(sysdate, 'MM')+rownum-1 as wwm from user_objects where rownum < 366) where
to_char(wwm,'D')=1 ) sunday,
(select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day from (select
trunc(sysdate, 'MM')+rownum-1 as wwm from user_objects where rownum < 366) where
to_char(wwm,'D')=7 ) saturday
where sunday.the_week=saturday.the_week) a
where the_week=32

Answer 2

oracle中 我给出一个周数 用什么函数可以取出这周的开始日期和结束日期？
下面是代码:
CREATE OR REPLACE FUNCTION calcDates(START_DATE IN DATE, END_DATE IN DATE)
RETURN number IS
No_of_DAYS number;
BEGIN

IF START_DATE < END_DATE THEN
SELECT count(1) days
INTO NO_OF_DAYS
FROM (SELECT DISTINCT trunc(START_DATE) + level - 1 dayList
FROM dual
connect BY trunc(START_DATE) + level - 1 < = trunc(END_DATE)) A
where not exists (select 1
from dp_tbl_public_holiday b
where to_date(b.ph_date, 'YYYYMMDD') = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
ELSE
SELECT 0 - count(1) days
INTO NO_OF_DAYS
FROM (SELECT DISTINCT trunc(END_DATE) + level - 1 dayList
FROM dual
connect BY trunc(END_DATE) + level - 1 < = trunc(START_DATE)) A
where not exists (select 1
from dp_tbl_public_holiday b
where to_date(b.ph_date, 'YYYYMMDD') = A.dayList)
and to_char(dayList, 'D') not in (1, 7);
END IF;

Return No_of_DAYS;
END;

Answer 3

SQL> select trunc(a)-6 begin_day,trunc(a) end_day from (
2 SELECT b.a,rownum rn
3 FROM (SELECT trunc(SYSDATE,'yyyy')+ROWNUM a
4 FROM dba_objects where rownum<=366) b
5 WHERE to_char(b.a,'day')='星期六')
6 where rn=32 ;

BEGIN_DAY END_DAY
-------------- --------------
01-8月 -10 07-8月 -10

SQL> alter session set nls_date_format='yyyy-mm-dd' ;

会话已更改。

SQL> select trunc(a)-6 begin_day,trunc(a) end_day from (
2 SELECT b.a,rownum rn
3 FROM (SELECT trunc(SYSDATE,'yyyy')+ROWNUM a
4 FROM dba_objects where rownum<=366) b
5 WHERE to_char(b.a,'day')='星期六')
6 where rn=32 ;

BEGIN_DAY END_DAY
---------- ----------
2010-08-01 2010-08-07