表A中有两个字段time1,time2 ,都是字符形数据
time1 time2
2006-12-04 10:18:09 2006-12-07
2006-12-08 11:45:04 2006-12-09
2006-12-10 23:18:01 2006-12-11
time1取到日为止即:2006-12-04、2006-12-08、2006-12-10
选出time2比time1大24小时的数据
结果为
time1 time2 2006-12-08 11:45:04 2006-12-09
2006-12-10 23:18:01 2006-12-11
time1 time2
2006-12-04 10:18:09 2006-12-07
2006-12-08 11:45:04 2006-12-09
2006-12-10 23:18:01 2006-12-11
time1取到日为止即:2006-12-04、2006-12-08、2006-12-10
选出time2比time1大24小时的数据
结果为
time1 time2 2006-12-08 11:45:04 2006-12-09
2006-12-10 23:18:01 2006-12-11
在相减
*
from tablewhere to_date(time2,'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(time1,'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')>=1
TRUNC(TO_DATE('2006-12-08 11:10:11','yyyy-mm-dd hh24:mi:ss') )
FROM dual
可以把这个作为条件
where TO_DATE('2006-12-09','yyyy-mm-dd hh24:mi:ss') -
TRUNC(TO_DATE('2006-12-08 11:10:11','yyyy-mm-dd hh24:mi:ss') ) >=1
time2
from a
where datediff(hour , cast ( substring(time1 , 1 , 10 ) as datetime) ,cast( time2 as datetime ) ) >= 24
SQL> select to_char(to_date('2006-10-11','yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(TO_DATE('2006-10-11','
------------------------------
2006-10-11 00:00:00
SELECT time1,time2 FROM table_name where TO_DATE(time2,'yyyy-mm-dd')-TO_DATE(time1,'yyyy-mm-dd hh24:mi:ss')>2如果time1,time2是日期型,
SELECT time1,time2 FROM table_name where time2-time1>2分两种情况,应该可行了