Oracle选择今天之间的日期

问题描述：

我有一个像这样的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选择今天之间的日期

相关推荐