How to compare two dates from an oracle database

I am trying to select rows from a table based on a date inside the table using the SQL query: SELECT * FROM users WHERE date1 = TO_DATE('01/01/2020', 'MM/DD/YYYY') . For some reason, this query only works for some dates but not all of them and I do not know why. When displaying the dates, the format looks like ’01/01/2020 12:00:00 AM’, but I have tried manipulating the TO_DATE() function to factor in the time and it still does not change the outcome. Any help would be greatly appreciated, if it helps, some of the dates that work are: ’01/01/2001′ and ’01/01/2000′.

Data:

Does not Work

7/1/2000 12:00:00 AM | 7/31/2000 12:00:00 AM | 1
7/1/2000 12:00:00 AM | 7/31/2000 12:00:00 AM | 2
7/1/2000 12:00:00 AM | 7/31/2000 12:00:00 AM | 3
7/1/2000 12:00:00 AM | 7/31/2000 12:00:00 AM | 4
7/1/2000 12:00:00 AM | 7/31/2000 12:00:00 AM | 5
2/1/2000 12:00:00 AM | 2/31/2000 12:00:00 AM | 1
2/1/2000 12:00:00 AM | 2/31/2000 12:00:00 AM | 2
2/1/2000 12:00:00 AM | 2/31/2000 12:00:00 AM | 3
2/1/2000 12:00:00 AM | 2/31/2000 12:00:00 AM | 4
2/1/2000 12:00:00 AM | 2/31/2000 12:00:00 AM | 5

Works

1/1/2001 12:00:00 AM | 1/31/2001 12:00:00 AM | 1
1/1/2001 12:00:00 AM | 1/31/2001 12:00:00 AM | 2
1/1/2001 12:00:00 AM | 1/31/2001 12:00:00 AM | 3
1/1/2001 12:00:00 AM | 1/31/2001 12:00:00 AM | 4
1/1/2001 12:00:00 AM | 1/31/2001 12:00:00 AM | 5
1/1/2000 12:00:00 AM | 1/31/2000 12:00:00 AM | 1

Answer

If you want to select one day of data from a DATE column, that can contain a tiem component, use

SELECT * FROM users WHERE date1 >= DATE'2020-01-01' and date1 < DATE'2020-01-02'

The subtle difference to using TRUNC is that you may use index access if appropriate.

Advertisement

Answer