WHERE DATECOL BETWEEN TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD')||' 05:20:00','YYYYMMDD HH24:MI:SS') AND TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD')||' 06:20:00','YYYYMMDD HH24:MI:SS')
查这个时间段的所有业务信息,关键是时间不知道该怎么处理?能不能只截取date字段中的时间啊 比方说我要查7.1日到7.30日每天5:00到6:00的业务信息,我可以这么写 select * from order_list where time > to_date('04-07-01 05:00','yyyy-mm-dd hh24:mi:ss') and time < to_date('04-07-01 06:00','yyyy-mm-dd hh24:mi:ss') or time > to_date('04-07-02 05:00','yyyy-mm-dd hh24:mi:ss') and time < to_date('04-07-02 06:00','yyyy-mm-dd hh24:mi:ss') or .. .. or time > to_date('04-07-30 05:00','yyyy-mm-dd hh24:mi:ss') and time < to_date('04-07-30 06:00','yyyy-mm-dd hh24:mi:ss')但这也太烦了,有没有更好的办法呢
select * from order_list where to_char(time,hh24:mi') > '05:20' and to_char(time,hh24:mi') > '06:20' and to_char(date,'yyyy/mm/dd')<to_char(SYSDATE-30,'yyyy/mm/dd')
同意 midaszmm(mm) where to_char(time,'yyyymm') = to_char(SYSDATE ,'yyyymm') and to_char( time ,'hh24:mi') >= '05:20' and to_char(time ,'hh24:mi') <= '06:20' 建议 先判断本月 在判断时间 最好按月份建FBI索引先
select * from order_list where to_char(time,hh24:mi') > '05:20' and to_char(time,hh24:mi') > '06:20' and to_char(date,'yyyymmdd')>to_char(date,'yyyy')||to_char(to_number(to_char(date,'mm'))-1)||to_char(date,'dd')
比方说我要查7.1日到7.30日每天5:00到6:00的业务信息,我可以这么写
select * from order_list
where time > to_date('04-07-01 05:00','yyyy-mm-dd hh24:mi:ss') and
time < to_date('04-07-01 06:00','yyyy-mm-dd hh24:mi:ss')
or
time > to_date('04-07-02 05:00','yyyy-mm-dd hh24:mi:ss') and
time < to_date('04-07-02 06:00','yyyy-mm-dd hh24:mi:ss')
or
..
..
or
time > to_date('04-07-30 05:00','yyyy-mm-dd hh24:mi:ss') and
time < to_date('04-07-30 06:00','yyyy-mm-dd hh24:mi:ss')但这也太烦了,有没有更好的办法呢
where to_char(time,hh24:mi') > '05:20' and
to_char(time,hh24:mi') > '06:20' and
to_char(date,'yyyy/mm/dd')<to_char(SYSDATE-30,'yyyy/mm/dd')
先判断当前月份有多少天(这个有函数可以实现),然后再按你说的循环来生成WHERE 子句,然后在跟SELECT合并 执行,不过可能这样速度会很慢,如果现在没找到更好的办法不妨试试看。试完了告诉我一下结果。
where to_char(time,'yyyymm') = to_char(SYSDATE ,'yyyymm') and
to_char( time ,'hh24:mi') >= '05:20' and
to_char(time ,'hh24:mi') <= '06:20'
建议 先判断本月 在判断时间
最好按月份建FBI索引先
where to_char(time,hh24:mi') > '05:20' and
to_char(time,hh24:mi') > '06:20' and
to_char(date,'yyyymmdd')>to_char(date,'yyyy')||to_char(to_number(to_char(date,'mm'))-1)||to_char(date,'dd')