select next_day(trunc(sysdate,'mm'),'星期一'),next_day(next_day(trunc(sysdate,'mm'),'星期一'),'星期日') from dual
上句话自行测试,算是本月的某一星期,从星期一到星期日的日期,我如果想知道这个月第三周,或第四周,分别是哪到至哪天,上面SQL的参数应该怎么写呢?
上句话自行测试,算是本月的某一星期,从星期一到星期日的日期,我如果想知道这个月第三周,或第四周,分别是哪到至哪天,上面SQL的参数应该怎么写呢?
select next_day(next_day(next_day(trunc(sysdate,'mm'),'Monday'),'Monday'),'Monday'),next_day(next_day(next_day(next_day(trunc(sysdate,'mm'),'Monday'),'Monday'),'Monday'),'Sunday') from dual;
第四周
select next_day(next_day(next_day(next_day(trunc(sysdate,'mm'),'Monday'),'Monday'),'Monday'),'Monday'),next_day(next_day(next_day(next_day(next_day(trunc(sysdate,'mm'),'Monday'),'Monday'),'Monday'),'Monday'),'Sunday') from dual;
SQL> SELECT *
2 FROM (SELECT (SYSDATE - TO_CHAR(SYSDATE, 'dd') + ROWNUM) DATETIME
3 FROM DUAL
4 CONNECT BY ROWNUM <= 31) TMP
5 WHERE TO_CHAR(TMP.DATETIME, 'W') = 1;
DATETIME
-----------
2010-08-01
2010-08-02
2010-08-03
2010-08-04
2010-08-05
2010-08-06
2010-08-07
7 rows selected
SQL>
DATETIME
-----------
2010-08-08
2010-08-09
2010-08-10
2010-08-11
2010-08-12
2010-08-13
2010-08-14
7 rows selected
SQL>
SQL> SELECT *
2 FROM (SELECT (SYSDATE - TO_CHAR(SYSDATE, 'dd') + ROWNUM) DATETIME
3 FROM DUAL
4 CONNECT BY ROWNUM <= 31) TMP
5 WHERE TO_CHAR(TMP.DATETIME, 'W') = 3;
DATETIME
-----------
2010-08-15
2010-08-16
2010-08-17
2010-08-18
2010-08-19
2010-08-20
2010-08-21
7 rows selected
SQL>
SQL> SELECT *
2 FROM (SELECT (SYSDATE - TO_CHAR(SYSDATE, 'dd') + ROWNUM) DATETIME
3 FROM DUAL
4 CONNECT BY ROWNUM <= 31) TMP
5 WHERE TO_CHAR(TMP.DATETIME, 'W') = 4;
DATETIME
-----------
2010-08-22
2010-08-23
2010-08-24
2010-08-25
2010-08-26
2010-08-27
2010-08-28
7 rows selected
SQL>
SQL> SELECT *
2 FROM (SELECT (SYSDATE - TO_CHAR(SYSDATE, 'dd') + ROWNUM) DATETIME
3 FROM DUAL
4 CONNECT BY ROWNUM <= 31) TMP
5 WHERE TO_CHAR(TMP.DATETIME, 'W') = 5;
DATETIME
-----------
2010-08-29
2010-08-30
2010-08-31
declare
start_day date;
weeks number(1):=&w;
begin
start_day:=next_day(trunc(sysdate,'mm'),'monday');
dbms_output.put_line(to_char(weeks)||' Week of '||to_char(sysdate,'yyyy-mm')||':');
dbms_output.put_line(
'Monday is '||to_char(start_day+(weeks-1)*7,'yyyy-mm-dd')||'; '||
'Sunday is '||to_char(start_day+weeks*7-1,'yyyy-mm-dd')||'.');
end;
/