I have a table with a date column (DD/MM/YYYY hh:mm:ss) as follows:

02/09/2021 09:50:37
03/09/2021 09:20:27
02/09/2021 14:05:25
03/09/2021 12:50:28
02/09/2021 14:25:26

What's the most efficient way to filter by date?

Note: I tried with

select date 
from table
where date = '02/09/2021'

However, this provides results that wrongly lack the time and only contain the date 02/09/2021.

For example, 02/09/2021 09:50:37 would not be returned.

CodePudding user response：

A simple option is

select * 
from your_table
where trunc(date_column) = date '2021-09-02'

If there's an index on date_column, it wouldn't be used in that case so performance might suffer so you'll have to either create a function-based index, or use a different where clause:

where date_column >= date '2021-09-02' and date_column < date '2021-03-03'

CodePudding user response：

If you want to return every record that has some date column equal to some date regardless to the time part then you need this:

select date 
from table
where trunc(date) = '02/09/2021'

please avoid naming columns date and tables table. Here is a small demo

CodePudding user response：

Use a date range:

SELECT date_column
FROM   table_name
WHERE  date_column >= DATE '2021-09-02'
AND    date_column <  DATE '2021-09-02'   INTERVAL '1' DAY;

This will enable Oracle to use an index on the date_column column.

If you filter using:

• TRUNC(date_column) = DATE '2021-09-02' or
• TO_CHAR(date_column, 'DD-MM-YYYY') = '02-09-2021'

Then Oracle will not use an index on the date_column and would need a separate function-based index on either TRUNC(date_column) or TO_CHAR(date_column, 'DD-MM-YYYY').