甲骨文日期“在...之间"询问 [英] Oracle date "Between" Query
问题描述
我正在使用oracle数据库.我想执行一个查询来检查两个日期之间的数据.
I am using oracle database. i want to execute one query to check the data between two dates.
NAME START_DATE
------------- -------------
Small Widget 15-JAN-10 04.25.32.000000 PM
Product 1 17-JAN-10 04.31.32.000000 PM
select * from <TABLENAME> where start_date
BETWEEN '15-JAN-10' AND '17-JAN-10'
但是我没有从上述查询中得到任何结果.我认为我必须使用喜欢"和%".但是我不知道在哪里使用它们.请对此点灯.
But I dont get any results from above query. I think I have to use "like" and "%". But I dont know where to use them. Please throw some lights on this.
提前谢谢.
推荐答案
从输出中判断,您好像已将START_DATE定义为时间戳.如果是正常日期,Oracle将能够处理隐式转换.但不是,您需要将这些字符串显式转换为日期.
Judging from your output it looks like you have defined START_DATE as a timestamp. If it were a regular date Oracle would be able to handle the implicit conversion. But as it isn't you need to explicitly cast those strings to be dates.
SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
2 /
Session altered.
SQL>
SQL> select * from t23
2 where start_date between '15-JAN-10' and '17-JAN-10'
3 /
no rows selected
SQL> select * from t23
2 where start_date between to_date('15-JAN-10') and to_date('17-JAN-10')
3 /
WIDGET START_DATE
------------------------------ ----------------------
Small Widget 15-JAN-10 04.25.32.000
SQL>
但是我们仍然只能得到一行.这是因为START_DATE具有时间元素.如果不指定时间部分,Oracle会将其默认为午夜.对于BETWEEN
的 from 端来说是很好的,但对于 until 端来说不是:
But we still only get one row. This is because START_DATE has a time element. If we don't specify the time component Oracle defaults it to midnight. That is fine for the from side of the BETWEEN
but not for the until side:
SQL> select * from t23
2 where start_date between to_date('15-JAN-10')
3 and to_date('17-JAN-10 23:59:59')
4 /
WIDGET START_DATE
------------------------------ ----------------------
Small Widget 15-JAN-10 04.25.32.000
Product 1 17-JAN-10 04.31.32.000
SQL>
修改
如果您无法传递时间部分,则有两种选择.一种是更改WHERE子句以从条件中删除时间元素:
If you cannot pass in the time component there are a couple of choices. One is to change the WHERE clause to remove the time element from the criteria:
where trunc(start_date) between to_date('15-JAN-10')
and to_date('17-JAN-10')
这可能会影响性能,因为它在START_DATE取消了所有b树索引的资格.您将需要构建基于函数的索引.
This might have an impact on performance, because it disqualifies any b-tree index on START_DATE. You would need to build a function-based index instead.
或者,您也可以在代码中的日期中添加时间元素:
Alternatively you could add the time element to the date in your code:
where start_date between to_date('15-JAN-10')
and to_date('17-JAN-10') + (86399/86400)
由于这些问题,许多人更喜欢通过检查日期边界来避免使用between
:
Because of these problems many people prefer to avoid the use of between
by checking for date boundaries like this:
where start_date >= to_date('15-JAN-10')
and start_date < to_date('18-JAN-10')
这篇关于甲骨文日期“在...之间"询问的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!