ORACLE的时间查询问题

[bihu]

查询时间范围用SQL语言：
SELECT * FROM table WHERE time BETWEEN
varstartyear-varstartmonth-varstartday 上午 00:00:01 AND varendyear-varendmonth-varendday 下午 11:59:59
提示：ORA-00905：缺少关键字
此命令含有一个以上错误

[xieyongan]

BETWEEN 不是日期函数的关键字，用 MONTHS_BETWEEN

• 打赏
• 举报

回复 赞

[smartdonkey]

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')

• 打赏
• 举报

回复 赞

[KingSunSha]

语法完全不对，日期至少至少要用括号括起来吧！！！
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('20020114','yyyymmdd') and to_date('20020116','yyyymmdd');

• 打赏
• 举报

回复 赞

[xinpingf]

日期括不括起来倒不是很重要，主要是要让系统能当个日期认它，
如果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一起出现的

• 打赏
• 举报

回复 赞