Oracle选择今天之间的日期 [英] Oracle Select Where Date Between Today
问题描述
我有一个像这样的Oracle SELECT查询:
I have a Oracle SELECT Query like this:
Select * From Customer_Rooms CuRo
Where CuRo.Date_Enter Between 'TODAY 12:00:00 PM' And 'TODAY 11:59:59 PM'
我的意思是,我想选择"date_enter"字段今天所在的所有位置.我已经尝试过使用Trunc(Sysdate) || ' 12:00:00'
之类的方法,但是没有用.
I mean, I want to select all where the field "date_enter" is today. I already tried things like Trunc(Sysdate) || ' 12:00:00'
in the between, didn't work.
建议:我不能使用TO_CHAR,因为它变得太慢了.
Advice: I can't use TO_CHAR because it gets too slow.
推荐答案
假定date_enter
是DATE
字段:
Select * From Customer_Rooms CuRo
Where CuRo.Date_Enter >= trunc(sysdate)
And CuRo.Date_Enter < trunc(sysdate) + 1;
默认情况下,trunc()
函数会删除时间部分,因此trunc(sysdate)
会在今天早上给您提供午夜时间.
The trunc()
function strips out the time portion by default, so trunc(sysdate)
gives you midnight this morning.
如果您特别想坚持使用between
,并且拥有DATE
而不是TIMESTAMP
,则可以执行以下操作:
If you particularly want to stick with between
, and you have a DATE
not a TIMESTAMP
, you could do:
Select * From Customer_Rooms CuRo
Where CuRo.Date_Enter between trunc(sysdate)
And trunc(sysdate) + interval '1' day - interval '1' second;
between
是包容性的,因此,如果您不休假,那么您有可能会从今晚的午夜准确地获取记录;因此,这会生成您在原始查询中寻找的23:59:59时间.但无论如何,我认为使用>=
和<
会更清晰,更明确.
between
is inclusive, so if you don't take a second off then you'd potentially pick up records from exactly midnight tonight; so this generates the 23:59:59 time you were looking for in your original query. But using >=
and <
is a bit clearer and more explicit, in my opinion anyway.
如果您确定无论如何都不能晚于今天的日期,则上限实际上并没有添加任何内容,并且只需使用以下内容即可得到相同的结果:
If you're sure you can't have dates later than today anyway, the upper bound isn't really adding anything, and you'd get the same result with just:
Select * From Customer_Rooms CuRo
Where CuRo.Date_Enter >= trunc(sysdate);
但是,您不想在date_enter
列上使用trunc
或to_char
;使用任何函数都会阻止使用该列上的索引,这就是使用to_char
进行查询的速度太慢的原因.
You don't want to use trunc
or to_char
on the date_enter
column though; using any function prevents an index on that column being used, which is why your query with to_char
was too slow.
这篇关于Oracle选择今天之间的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!