select * from tbl where dtime between to_date('2011-10-01', 'yyyy-mm-dd') - 1 + 18/24 and to_date('2011-10-01', 'yyyy-mm-dd') + 6/24;
这更简单select * from tbl where to_char(dtime, 'hh24') <= '06' and to_char(dtime, 'hh24') >= '18' and trunc(dtime) = to_date('2011-10-01', 'yyyy-mm-dd');
select * from table1 where to_char(dt, 'hh24') <= '06' and to_char(dt, 'hh24') >= '18' and dt between to_date('2011-10-01', 'yyyy-mm-dd') and between to_date('2011-10-02', 'yyyy-mm-dd');
只需要修改 and trunc(dtime) = to_date('2011-10-01', 'yyyy-mm-dd'); 改成 and trunc(dtime) >= to_date('2011-10-01', 'yyyy-mm-dd') and trunc(dtime) < to_date('2011-10-10', 'yyyy-mm-dd')
我的时间是varchar2的类型。我写的是这样的。但是查询不出来数据。表名:algaehistorydata时间列:recordtime select * from algaehistorydata where to_number(to_char(recordtime,'yyyy-MM-dd hh24:mi:ss','hh24')) <= to_number('06') and to_number(to_char(recordtime,'yyyy-MM-dd hh24:mi:ss','hh24')) >= to_number('18') and trunc(to_date(recordtime,'yyyy-MM-dd')) >= to_date('2011-01-01', 'yyyy-mm-dd') and trunc(to_date(recordtime,'yyyy-MM-dd')) < to_date('2011-10-10', 'yyyy-mm-dd')
-- 首先你的时间是char的就不要要再to_char了,只需要使用substr就可以了,前提是要保证你的时间的格式都是相同的,不会有异常格式的时间; -- 其次字符串也是可以进行大小比较的,不需要再转换为number,前提是字符串左侧补0; -- 还有<=06和>18两个where条件之间的逻辑关系应当是or而不是and; -- 所以适合你用的SQL如下: select * from algaehistorydata where (substr(recordtime,6,2) <= '06' or substr(recordtime,6,2) >= '18') and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) >= to_date('2011-01-01', 'yyyy-mm-dd') and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) < to_date('2011-10-10', 'yyyy-mm-dd')
上面的SQL有点错误,字符串截取时间的位置计算有错,需要修改。 select * from algaehistorydata where (substr(recordtime,12,2) <= '06' or substr(recordtime,12,2) >= '18') and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) >= to_date('2011-01-01', 'yyyy-mm-dd') and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) < to_date('2011-10-10', 'yyyy-mm-dd')
select * from tbl
where dtime between to_date('2011-10-01', 'yyyy-mm-dd') - 1 + 18/24 and to_date('2011-10-01', 'yyyy-mm-dd') + 6/24;
这更简单select * from tbl
where to_char(dtime, 'hh24') <= '06'
and to_char(dtime, 'hh24') >= '18'
and trunc(dtime) = to_date('2011-10-01', 'yyyy-mm-dd');
select * from table1
where to_char(dt, 'hh24') <= '06'
and to_char(dt, 'hh24') >= '18'
and dt between to_date('2011-10-01', 'yyyy-mm-dd') and between to_date('2011-10-02', 'yyyy-mm-dd');
只需要修改 and trunc(dtime) = to_date('2011-10-01', 'yyyy-mm-dd');
改成 and trunc(dtime) >= to_date('2011-10-01', 'yyyy-mm-dd')
and trunc(dtime) < to_date('2011-10-10', 'yyyy-mm-dd')
select * from algaehistorydata
where to_number(to_char(recordtime,'yyyy-MM-dd hh24:mi:ss','hh24')) <= to_number('06')
and to_number(to_char(recordtime,'yyyy-MM-dd hh24:mi:ss','hh24')) >= to_number('18')
and trunc(to_date(recordtime,'yyyy-MM-dd')) >= to_date('2011-01-01', 'yyyy-mm-dd')
and trunc(to_date(recordtime,'yyyy-MM-dd')) < to_date('2011-10-10', 'yyyy-mm-dd')
-- 其次字符串也是可以进行大小比较的,不需要再转换为number,前提是字符串左侧补0;
-- 还有<=06和>18两个where条件之间的逻辑关系应当是or而不是and;
-- 所以适合你用的SQL如下:
select * from algaehistorydata
where (substr(recordtime,6,2) <= '06' or substr(recordtime,6,2) >= '18')
and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) >= to_date('2011-01-01', 'yyyy-mm-dd')
and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) < to_date('2011-10-10', 'yyyy-mm-dd')
select * from algaehistorydata
where (substr(recordtime,12,2) <= '06' or substr(recordtime,12,2) >= '18')
and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) >= to_date('2011-01-01', 'yyyy-mm-dd')
and trunc(to_date(recordtime,'yyyy-MM-dd hh24:mi:ss'')) < to_date('2011-10-10', 'yyyy-mm-dd')