求助，本月第几周怎么算，非常规算，内详，谢谢！

Question

常规计算
select  to_number(to_char(sysdate,'W')) from dual;这样取的效果就是本月1-7号为第一周，8-14号为第二周，依次类推
现要求
比如2010年8月1号是星期天，8月1号为8月第一周，
8月2号星期一，即8月2号开始为8月第二周，这个怎么算？
谢谢先！

Answer 1

SELECT DECODE(TO_CHAR(SYSDATE, 'day'),
              '星期一',
              TO_NUMBER(TO_CHAR(SYSDATE, 'W')) ,
              TO_NUMBER(TO_CHAR(SYSDATE, 'W'))+1)
  FROM DUAL;

Answer 2

-- 加个 cas when 不就得了？

Answer 3

SELECT DECODE(TO_CHAR(to_date('20100801','yymmdd'), 'day'),
              '星期一',
              TO_NUMBER(TO_CHAR(to_date('20100801','yymmdd'), 'W')) ,
              TO_NUMBER(TO_CHAR(to_date('20100801','yymmdd'), 'W'))+1)
  FROM DUAL;
---结果是2，应该是1啊

Answer 4

SQL> Select DECODE(to_char(trunc(SYSDATE,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE),trunc(SYSDATE,'MM'),1,to_char(sysdate,'W')+1)),to_char(sysdate,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE,'
------------------------------
                             2
 
SQL> Select DECODE(to_char(trunc(SYSDATE-4,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE-4),trunc(SYSDATE-4,'MM'),1,to_char(SYSDATE-4,'W')+1)),to_char(SYSDATE-4,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-4
------------------------------
                             5
 
SQL> Select DECODE(to_char(trunc(SYSDATE-3,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE-3),trunc(SYSDATE-3,'MM'),1,to_char(SYSDATE-3,'W')+1)),to_char(SYSDATE-3,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-3
------------------------------
                             1
 
SQL> SELECT SYSDATE FROM DUAL;
 
SYSDATE
-----------
2010-8-4 18
 
SQL> 

Answer 5

我服务器时间有点问题，少一天，功能复合要求，就是感觉太麻烦了
SQL> Select DECODE(to_char(trunc(SYSDATE,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE),trunc(SYSDATE,'MM'),1,to_char(sysdate,'W')+1)),to_char(sysdate,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE,
------------------------------
                             2
 
SQL> Select DECODE(to_char(trunc(SYSDATE-4,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE-4),trunc(SYSDATE-4,'MM'),1,to_char(SYSDATE-4,'W')+1)),to_char(SYSDATE-4,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-4
------------------------------
                             5
 
SQL> Select DECODE(to_char(trunc(SYSDATE-3,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE-3),trunc(SYSDATE-3,'MM'),1,to_char(SYSDATE-3,'W')+1)),to_char(SYSDATE-3,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-3
------------------------------
                             1
 
SQL> SELECT SYSDATE FROM DUAL;
 
SYSDATE
-----------
2010-8-4 18
 
SQL> 

Answer 6

先判断本月第一天是不是周日，如果不是则取to_char(SYSDATE-3,'W')即符合要求，如果是，则判断当前时间，当前时间是本月第一天，则返回1，否则返回to_char(SYSDATE-3,'W'))+1

Answer 7

SELECT DECODE(TO_CHAR(SYSDATE, 'day'),
              '星期日',
              TO_NUMBER(TO_CHAR(SYSDATE, 'W')) ,
              TO_NUMBER(TO_CHAR(SYSDATE, 'W'))+1) W
  FROM DUAL;

Answer 8

SQL> Select DECODE(to_char(trunc(SYSDATE,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE),trunc(SYSDATE,'MM'),1,to_char(sysdate,'W')+1)),to_char(sysdate,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE,'
------------------------------
                             2
 
SQL> Select DECODE(to_char(trunc(SYSDATE-4,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE-4),trunc(SYSDATE-4,'MM'),1,to_char(SYSDATE-4,'W')+1)),to_char(SYSDATE-4,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-4
------------------------------
                             5
 
SQL> Select DECODE(to_char(trunc(SYSDATE-3,'MM'),'D'),1,(DECODE(TRUNC(SYSDATE-3),trunc(SYSDATE-3,'MM'),1,to_char(SYSDATE-3,'W')+1)),to_char(SYSDATE-3,'W')) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-3
------------------------------
                             1
 
SQL> SELECT SYSDATE FROM DUAL;
 
SYSDATE
-----------
2010-8-4 18
 
SQL> 

Answer 9

-- 写个函数给你吧：
CREATE OR REPLACE FUNCTION week_month(v_date DATE)
RETURN NUMBER
IS
  v_day number;
BEGIN
  v_day := to_char(trunc(v_date,'mm'),'D');
  return trunc((to_char(v_date,'DD')+6-v_day)/7)+1;
END;
/SELECT week_month(DATE'2010-08-01') from DUAL;SELECT week_month(DATE'2010-09-01') from DUAL;SELECT week_month(DATE'2010-09-05') from DUAL;

Answer 10

我记得我做过，做周统计的时候，做过这个算法，，SQL在公司呢，明天发发。。就是蛮麻烦的，

Answer 11

---------------------------------------------------------------------------------
-- *1). 按 星期日 是一周的起始日CREATE OR REPLACE FUNCTION week_month(v_date DATE)
RETURN NUMBER
IS
  v_day number;
BEGIN
  v_day := to_char(trunc(v_date,'mm'),'D');
  return trunc((to_char(v_date,'DD')+6-v_day)/7)+1;
END;
/---------------------------------------------------------------------------------
-- *2). 按 星期一 是一周的起始日CREATE OR REPLACE FUNCTION week_month(v_date DATE)
RETURN NUMBER
IS
  v_day number;
BEGIN
  v_day := mod(to_char(trunc(v_date,'mm'),'D'),6);
  return trunc((to_char(v_date,'DD')+6-v_day)/7)+1;
END;
/-- 测试 (按 星期一 是一周的起始日)：
scott@LYMORA> SELECT week_month(DATE'2010-08-01') from DUAL;WEEK_MONTH(DATE'2010-08-01')
----------------------------
                           1已选择 1 行。已用时间:  00: 00: 00.00
scott@LYMORA> SELECT week_month(DATE'2010-08-02') from DUAL;WEEK_MONTH(DATE'2010-08-02')
----------------------------
                           2已选择 1 行。已用时间:  00: 00: 00.00
scott@LYMORA> SELECT week_month(DATE'2010-08-07') from DUAL;WEEK_MONTH(DATE'2010-08-07')
----------------------------
                           2已选择 1 行。已用时间:  00: 00: 00.01
scott@LYMORA> SELECT week_month(DATE'2010-08-08') from DUAL;WEEK_MONTH(DATE'2010-08-08')
----------------------------
                           2已选择 1 行。已用时间:  00: 00: 00.00
scott@LYMORA> SELECT week_month(DATE'2010-08-09') from DUAL;WEEK_MONTH(DATE'2010-08-09')
----------------------------
                           3已选择 1 行。已用时间:  00: 00: 00.01
scott@LYMORA>

Answer 12

直接计算 weeks of year (based on the ISO standard) 之差，应该可以。select to_char(sysdate,'IW')-to_char(trunc(sysdate,'MM'),'IW')+1 from dual;

Answer 13

select to_char(to_date('20100104','yymmdd'),'DY'), to_char(to_date('20100104','yymmdd'),'IW')-to_char(trunc(to_date('20100104','yymmdd'),'MM'),'IW')+1 from dual;
---查出来结果是  星期一，-51
其他月份对了

Answer 14

20100705SELECT to_char(to_date('20100705','yymmdd'),'DY'), week_month(to_date('20100705','yymmdd')) from dual;
----星期一，应该是第2周的，可是不管用哪个函数，结果都是第1周

Answer 15

-- 1 月前几天可能会被计算到上一年select
(case when to_char(trunc(sysdate,'MM'),'IW')>52
 then (case when to_char(sysdate,'IW')>52 then 1 else to_number(to_char(sysdate,'IW'))+1 end)
 else to_number(to_char(sysdate,'IW')) end)
-
(case when to_char(trunc(sysdate,'MM'),'IW')>52 then 1 else to_number(to_char(trunc(sysdate,'MM'),'IW')) end)
+1
from dual;

Answer 16

谢谢各位！问题解决！
可执行函数
月周：CREATE OR REPLACE FUNCTION week_month(p_date DATE) RETURN NUMBER IS
        v_Result  NUMBER;
        v_datestr VARCHAR2(10);
        v_weekday NUMBER;
BEGIN
        v_datestr := to_char(p_date, 'yyyyMMdd');
        SELECT (TO_NUMBER(TO_CHAR(TO_DATE(SUBSTR(v_datestr, 1, 6) || '01', 'YYYYMMDD'), 'D'),
                                             99) - 1)
            INTO v_weekday
            FROM dual;
        IF v_weekday = 0 THEN
                v_weekday := 7;
        END IF;
        SELECT ceil((TO_NUMBER(SUBSTR(v_datestr, -2), 99) + v_weekday-1) / 7)
            INTO v_Result
            FROM dual;
        RETURN v_Result;
END;年周：CREATE OR REPLACE FUNCTION UDF_WEEKOFYEAR ( V_DATE DATE )  RETURN number
is
v_ret1 NUMBER;BEGIN SELECT TO_CHAR
       (DECODE
        (SIGN
         ((V_DATE
          + TO_NUMBER
          (DECODE(TO_CHAR(TRUNC(V_DATE,
                                   'YYYY'
                                   ),
                             'D'
                             ),
                     '1',
                     '8',
                     TO_CHAR(TRUNC(V_DATE,
                                   'YYYY'
                                   ),
                             'D'
                             )
                     )
              )
          - 2
          )
          - LAST_DAY(V_DATE)
          ),
         1,
         LAST_DAY(V_DATE),
         (V_DATE
         + TO_NUMBER(DECODE(TO_CHAR(TRUNC(V_DATE,
                                           'YYYY'
                                           ),
                                     'D'
                                     ),
                             '1',
                             '8',
                             TO_CHAR(TRUNC(V_DATE,
                                           'YYYY'
                                           ),
                                     'D'
                                     )
                             )
                      )
         - 2
         )
         ),
        'WW'
        )
  into v_ret1
  FROM DUAL;  return v_ret1;
END;

Answer 17

select case when to_date(&yymmdd,'yyyymmdd')>a.weekend then cnt1 else cnt2 end  weekcnt
from (
--取得月第一周的周日的日期,加1周数,正常周数
select next_day(ADD_MONTHS(last_day(to_date(&yymmdd,'yyyymmdd')),-1),1)as weekend,
to_number(to_char(to_date(&yymmdd,'yyyymmdd'),'W'))+1 as cnt1,
to_number(to_char(to_date(&yymmdd,'yyyymmdd'),'W')) as cnt2 
from dual
) a

Answer 18

刚刚去调试了下，能运行成功。
create or replace procedure get_weekday(cur_date  date)
is
month_begin date:=trunc(sysdate,'mm');
first_week  varchar2(20);
curr_day    varchar2(20):=to_char(cur_date,'dd');
begin
--获得第一周的星期日
select  case to_char(month_begin,'day')
        when '星期日' then to_char(month_begin,'dd')
        else to_char(to_number(to_char(month_begin,'dd'))+(7-to_number(to_char(month_begin,'d'))))
        end
        into first_week from dual;
        --根据用户输入的日期来获得该日期所在哪个周
        if curr_day<first_week then
        dbms_output.put_Line(1);
        elsif curr_day>first_week and curr_day<first_week + 7 then
        dbms_output.put_Line(2);
        elsif curr_day<first_week+14 then
        dbms_output.put_line(3);
        elsif curr_day<first_week+21 then
        dbms_output.put_Line(4);
        elsif curr_day<first_week+28 then
        dbms_output.put_Line(5);
        elsif curr_day<31 then
        dbms_output.put_Line(5);
        end if;
end;运行结果如下：
SQL> set serveroutput on;
SQL> exec get_weekday(date'2010-8-1');1PL/SQL procedure successfully completedSQL> exec get_weekday(date'2010-8-2');2PL/SQL procedure successfully completedSQL> exec get_weekday(date'2010-8-10');3PL/SQL procedure successfully completedSQL> 

Answer 19

更正一下： if curr_day<first_week then 改成 if curr_day<=first_week then 

Answer 20

[Quote=引用 31 楼 dream_1986 的回复:]
引用 30 楼 java3344520 的回复:错了，是30楼，更改下。