查询时间范围用SQL语言:
SELECT * FROM table WHERE time BETWEEN
varstartyear-varstartmonth-varstartday 上午 00:00:01 AND varendyear-varendmonth-varendday 下午 11:59:59
提示:ORA-00905:缺少关键字
此命令含有一个以上错误
...全文
2555打赏收藏
ORACLE的时间查询问题
查询时间范围用SQL语言: SELECT * FROM table WHERE time BETWEEN varstartyear-varstartmonth-varstartday 上午 00:00:01 AND varendyear-varendmonth-varendday 下午 11:59:59 提示:ORA-00905:缺少关键字 此命令含有一个以上错误
日期括不括起来倒不是很重要,主要是要让系统能当个日期认它,
如果nls_date_language='DD-MON-YYYY'
你完全可以用
select * from table where time between '01-jan-2002' and '31-dec-2002';
当然了,如果你没有设置nls_date_language,最好还是用弱水三千的写法,不过上面的写法也有点问题……am和pm是不能和hh24一起出现的
语法完全不对,日期至少至少要用括号括起来吧!!!
SELECT * FROM table WHERE time BETWEEN
to_date('01/01/02 00:00:01AM','dd/mm/yy hh24:mi:ss') AND to_date('01/02/02 11:69:59PM','dd/mm/yy hh24:mi:ss')
其实通常的写法是这样的:
SELECT * FROM table WHERE TRUNC(time) BETWEEN to_date('01/01/02','dd/mm/dd') AND to_date('01/02/02','dd/mm/yy')
用trunc()把时间过滤掉
SELECT * FROM table WHERE time BETWEEN
to_date(varstartyear-varstartmonth-varstartday 00:00:01,'yyyy-mm-dd hh24:mi:ss') AND to_date(varendyear-varendmonth-varendday 23:59:59,'yyyy-mm-dd hh24:mi:ss')
-------------
SELECT * FROM table WHERE time BETWEEN
to_date(varstartyear-varstartmonth-varstartday 上午 00:00:01,'yyyy-mm-dd a.m. hh24:mi:ss') AND to_date(varendyear-varendmonth-varendday 下午 23:59:59,'yyyy-mm-dd p.m. hh24:mi:ss')