CREATE TABLE test6 (startdate date,enddate date); INSERT INTO test6 VALUES(To_Date('2011-04-01','yyyy-mm-dd'),To_Date('2011-04-30','yyyy-mm-dd')); COMMIT; SELECT * FROM test6; select * from ( select startdate+level-1 rq from test6 connect by level<=enddate - startdate+1 ) where to_char(rq,'day') not in ('星期六','星期日')
--或者直接一个SQL,不嵌套select startdate+level-1 rq from test6 where to_char(startdate+level-1,'day') not in ('星期六','星期日') connect by level<=enddate - startdate+1
如何用一条sql语句去掉两个日期中的周末
eygle@SZTYORA> select to_char(sysdate,'day') from dual;TO_CHAR(SYSDATE,'DAY') ------------------------ 星期一eygle@SZTYORA> alter session set nls_date_format=american; ERROR: ORA-01821: 日期格式无法识别 eygle@SZTYORA> alter session set nls_date_language=american;会话已更改。eygle@SZTYORA> select to_char(sysdate,'day') from dual;TO_CHAR(SYSDATE,'DAY') ------------------------------------------------------------------------ monday -- 用6楼的方法是不好的,因为不同的会话中,其日期会话语言可能不同,所以表示星期的结果也不一样! -- 所以最好用:where trunc(date_column,'D') not in (1,7);
------------------- ------------------------ --
2011-04-16 15:06:33 星期六 7
-- 星期六是一周的最后一天(第7天),所以:
where to_char(date_column,'D')=7-- 或者这样判断:(也许这样执行效率更高!)
where trunc(date_column,'dd')<>trunc(date_column,'D');
CREATE TABLE test6 (startdate date,enddate date);
INSERT INTO test6 VALUES(To_Date('2011-04-01','yyyy-mm-dd'),To_Date('2011-04-30','yyyy-mm-dd'));
COMMIT;
SELECT * FROM test6;
select * from (
select startdate+level-1 rq
from test6
connect by level<=enddate - startdate+1
)
where to_char(rq,'day') not in ('星期六','星期日')
--或者直接一个SQL,不嵌套select startdate+level-1 rq
from test6
where to_char(startdate+level-1,'day') not in ('星期六','星期日')
connect by level<=enddate - startdate+1
------------------------
星期一eygle@SZTYORA> alter session set nls_date_format=american;
ERROR:
ORA-01821: 日期格式无法识别
eygle@SZTYORA> alter session set nls_date_language=american;会话已更改。eygle@SZTYORA> select to_char(sysdate,'day') from dual;TO_CHAR(SYSDATE,'DAY')
------------------------------------------------------------------------
monday
-- 用6楼的方法是不好的,因为不同的会话中,其日期会话语言可能不同,所以表示星期的结果也不一样!
-- 所以最好用:where trunc(date_column,'D') not in (1,7);