17,377
社区成员
发帖
与我相关
我的任务
分享
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
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;
-- 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;
SELECT to_char(to_date('20100705','yymmdd'),'DY'), week_month(to_date('20100705','yymmdd')) from dual;
----星期一,应该是第2周的,可是不管用哪个函数,结果都是第1周
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
其他月份对了。。。
select to_char(sysdate,'IW')-to_char(trunc(sysdate,'MM'),'IW')+1 from dual;
---------------------------------------------------------------------------------
-- *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>
-- 写个函数给你吧:
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;