ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'; ALTER SESSION SET NLS_LANGUAGE=AMERICAN;SELECT CASE extract(year from lastday) WHEN 2009 THEN lastday ELSE lastday - INTERVAL '07' DAY END CASE FROM ( SELECT next_day('2009-12-31','SUNDAY') lastday FROM DUAL )
2009年1月4日是09年第一个星期天,select to_date('2008-12-28','yyyy-mm-dd hh24:mi:ss')+rownum*7 from dual connect by rownum<54
create table tb(id int,c_date date); insert into tb select rownum,rownum+sysdate d from all_objects where rownum<=365; commit;周日: select * from tb where to_char(c_date,'D')=1; 周六: select * from tb where to_char(c_date,'D')=7;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'; ALTER SESSION SET NLS_LANGUAGE=AMERICAN; SELECT * FROM ( SELECT to_date('20090101') + ROWNUM - 1 alldays FROM dual CONNECT BY rownum<=365 ) WHERE TRIM(to_char(alldays,'DAY')) IN ('SATURDAY', 'SUNDAY')
select alldays,decode(to_char(alldays,'D'),1,'星期日','星期六') from ( select to_date('2009'||rownum,'yyyyddd') alldays from all_objects where rownum<=decode(mod(2009,4),0,366,365) ) a where to_char(alldays,'D')='1' or to_char(alldays,'D')='7'
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;SELECT CASE extract(year from lastday)
WHEN 2009 THEN lastday
ELSE lastday - INTERVAL '07' DAY
END CASE
FROM (
SELECT next_day('2009-12-31','SUNDAY') lastday
FROM DUAL
)
connect by rownum<54
insert into tb
select rownum,rownum+sysdate d
from all_objects
where rownum<=365;
commit;周日:
select *
from tb
where to_char(c_date,'D')=1;
周六:
select *
from tb
where to_char(c_date,'D')=7;
ALTER SESSION SET NLS_LANGUAGE=AMERICAN; SELECT * FROM
(
SELECT to_date('20090101') + ROWNUM - 1 alldays
FROM dual
CONNECT BY rownum<=365
)
WHERE TRIM(to_char(alldays,'DAY'))
IN ('SATURDAY', 'SUNDAY')
select to_date('2009'||rownum,'yyyyddd') alldays
from all_objects
where rownum<=decode(mod(2009,4),0,366,365)
) a where to_char(alldays,'D')='1' or to_char(alldays,'D')='7'