Oracle Select Where Date Between Today

Question

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'

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.

Advice: I can't use TO_CHAR because it gets too slow.

Answer 1

Assuming date_enter is a DATE field:

Select * From Customer_Rooms CuRo
   Where CuRo.Date_Enter >= trunc(sysdate)
   And CuRo.Date_Enter < trunc(sysdate) + 1;

The trunc() function strips out the time portion by default, so trunc(sysdate) gives you midnight this morning.

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

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.

Answer 2

In my case, I was searching trough some log files and wanted to find only the ones that happened TODAY.

For me, it didn't matter what time it happened, just had to be today, so:

/*...*/
where
    trunc(_DATETIMEFROMSISTEM_) = trunc(sysdate)

It works perfectly for this scenario.