select trunc(sysdate,'yyyy') + mod(8 - to_number(to_char(trunc(sysdate,'yyyy'),'d')),7) + weeknum * 7 - 13 as mondyoftheweek from (select to_char(sysdate,'IW') as weeknum from dual);
select case when to_char(trunc(to_date('20090101','yyyymmdd'),'year'),'FMD') = 1 then trunc(to_date('20090101','yyyymmdd'),'year') + 7*(37-1) - 5 else trunc(to_date('20090101','yyyymmdd'),'year') + 7*37 - to_char(trunc(to_date('20090101','yyyymmdd'),'year'),'FMD')-5 end from dual; http://topic.csdn.net/t/20060916/15/5026554.html
select trunc(sysdate,'yyyy') + mod(8 - to_number(to_char(trunc(sysdate,'yyyy'),'d')),7) + weeknum * 7 - 13 as mondyoftheweek from (select to_char(sysdate,'IW') as weeknum from dual);
select next_day(to_date((select to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '0101', 'yyyymmdd') + ((select to_char(sysdate, 'iw') from dual) - 1) * 7,'yyyy-mm-dd') from dual),'yyyy-MM-dd'), 2) - 7, next_day(next_day(to_date((select to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '0101', 'yyyymmdd') + ((select to_char(sysdate, 'iw') from dual) - 1) * 7,'yyyy-mm-dd') from dual),'yyyy-MM-dd'), 2) - 7, 1) from dual
select trunc(sysdate,'yyyy') + mod(8 - to_number(to_char(trunc(sysdate,'yyyy'),'d')),7) + weeknum * 7 - 7 as sundayoftheweek from dual; 这里周日为第一天,若你想让周一为第一天,中间的to_number(to_char(trunc(sysdate,'yyyy'),'d'))进行转换就好了 select trunc(sysdate,'yyyy') + mod(9 - to_char(trunc(sysdate,'yyyy'),'d'),7) + weeknum * 7 - 14 as mondyoftheweek from dual; select trunc(sysdate,'yyyy') + mod(9 - to_char(trunc(sysdate,'yyyy'),'d'),7) + weeknum * 7 - 8 as sundyoftheweek from dual;
select to_char(sysdate,'IW') as weeknum ,TO_CHAR(sysdate-to_char(sysdate,'D')+1,'yyyy-MM-dd') wk1 ,TO_CHAR(sysdate-to_char(sysdate,'D')+6,'yyyy-MM-dd') wk7 from dual;
--这是以星期日为一周开始的 select to_char(sysdate,'IW') as week ,TO_CHAR(sysdate-to_char(sysdate,'D')+1,'yyyy-MM-dd') wk1 ,TO_CHAR(sysdate-to_char(sysdate,'D')+7,'yyyy-MM-dd') wk7 from dual;
from (select to_char(sysdate,'IW') as weeknum from dual);
then trunc(to_date('20090101','yyyymmdd'),'year') + 7*(37-1) - 5
else trunc(to_date('20090101','yyyymmdd'),'year') + 7*37 - to_char(trunc(to_date('20090101','yyyymmdd'),'year'),'FMD')-5 end
from dual; http://topic.csdn.net/t/20060916/15/5026554.html
from (select to_char(sysdate,'IW') as weeknum from dual);
next_day(next_day(to_date((select to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '0101', 'yyyymmdd') + ((select to_char(sysdate, 'iw') from dual) - 1) * 7,'yyyy-mm-dd') from dual),'yyyy-MM-dd'), 2) - 7, 1)
from dual
这里周日为第一天,若你想让周一为第一天,中间的to_number(to_char(trunc(sysdate,'yyyy'),'d'))进行转换就好了
select trunc(sysdate,'yyyy') + mod(9 - to_char(trunc(sysdate,'yyyy'),'d'),7) + weeknum * 7 - 14
as mondyoftheweek from dual;
select trunc(sysdate,'yyyy') + mod(9 - to_char(trunc(sysdate,'yyyy'),'d'),7) + weeknum * 7 - 8
as sundyoftheweek from dual;
,TO_CHAR(sysdate-to_char(sysdate,'D')+1,'yyyy-MM-dd') wk1
,TO_CHAR(sysdate-to_char(sysdate,'D')+6,'yyyy-MM-dd') wk7
from dual;
--这是以星期日为一周开始的
select to_char(sysdate,'IW') as week
,TO_CHAR(sysdate-to_char(sysdate,'D')+1,'yyyy-MM-dd') wk1
,TO_CHAR(sysdate-to_char(sysdate,'D')+7,'yyyy-MM-dd') wk7
from dual;