CODEWITHSUNDEEP
sqloracle-databaseselect
Felipe M
Felipe M

Reputation: 459

Oracle Select Where Date Between Today

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.

Upvotes: 9

Views: 37969

Answers (4)

Harendra Singh Rawat
Harendra Singh Rawat

Reputation: 9

Yes, you can write query like this 

select * from table_name where last_time<=CURRENT_TIME() and last_date<=CURRENT_DATE()

Upvotes: -1

PedroFTW
PedroFTW

Reputation: 84

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.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191255

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.

Upvotes: 20

prashant thakre
prashant thakre

Reputation: 5147

Try the below code 

Select * From Customer_Rooms CuRo
   Where trunc(CuRo.Date_Enter) = 'TODAY' -- TODAY is the date of today without any timestamp.

Upvotes: -2

Related Questions