select * from tab where time>to_date('20131209','yyyymmdd') and time<to_date('20140213','yyyymmdd') and (to_number(to_char(time,'hh24'))<9 or to_number(to_char(time,'hh24'))>=18 )这样?
设定日期字段为D,表名为T select * from T WHERE TRUNC(D) BETWEEN TO_DATE('2013-12-09','YYYY-MM-DD') AND TO_DATE('2014-02-12','YYYY-MM-DD') AND (TO_CHAR(D,'HH24MISS')>='180000' OR TO_CHAR(D,'HH24MISS')<='090000')
select * from tab where time>to_date('20131209','yyyymmdd') and time<to_date('20140213','yyyymmdd') and (to_number(to_char(time,'hh24'))<9 or to_number(to_char(time,'hh24'))>=18 ) and to_number(to_char(sysdate - 1,'d')>=1 and to_number(to_char(sysdate - 1,'d')<=5 )
select * from tab where time>to_date('20131209','yyyymmdd') and time<to_date('20140213','yyyymmdd') and (to_number(to_char(time,'hh24'))<9 or to_number(to_char(time,'hh24'))>=18 ) and to_number(to_char(sysdate - 1,'d')>=1 and to_number(to_char(sysdate - 1,'d')<=5 )
想得比楼主还周到,把周末剔除了, 不过应该是time-1 而不是 sysdate-1吧,呵呵
select * from tab where time>to_date('20131209','yyyymmdd') and time<to_date('20140213','yyyymmdd') and (to_number(to_char(time,'hh24'))<9 or to_number(to_char(time,'hh24'))>=18 ) and to_number(to_char(sysdate - 1,'d')>=1 and to_number(to_char(sysdate - 1,'d')<=5 )
select * from T
WHERE TRUNC(D) BETWEEN TO_DATE('2013-12-09','YYYY-MM-DD') AND TO_DATE('2014-02-12','YYYY-MM-DD')
AND (TO_CHAR(D,'HH24MISS')>='180000' OR TO_CHAR(D,'HH24MISS')<='090000')
)
)
想得比楼主还周到,把周末剔除了,
不过应该是time-1 而不是 sysdate-1吧,呵呵
)
想得比楼主还周到,把周末剔除了,
不过应该是time-1 而不是 sysdate-1吧,呵呵我是忘记了 to_number(to_char())写法正不正确了。 然后放到plsql里面去验证一下。 忘记改回来了。