How to compare two dates from an oracle database

Question

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

The date1 column is the ***DATE*** data type not a ***VARCHAR2*** — Gunnarhawk, Feb 25 '21 at 19:42
Please read through my answer for this question: https://stackoverflow.com/questions/64199263/my-to-date-seeming-not-to-function-properly/ I'm pretty sure you're running into the same issue. — Koen Lostrie, Feb 25 '21 at 19:45
@KoenLostrie I tried your solution but I am still getting the same issue. I changed my SQL to `SELECT * FROM users WHERE TRUNC(date1) = TO_DATE('01/01/2020 12:00:00', 'MM/DD/YYYY HH:MI:SS')` — Gunnarhawk, Feb 25 '21 at 20:00
@Gunnarhawk Hard to answer without more info. Could you provide a sample dataset for a couple of rows (2 that work, 2 that don't) ? You can use the export feature of sqldeveloper and export as insert statement. — Koen Lostrie, Feb 25 '21 at 20:14
@KoenLostrie I updated the post and added some test data. Let me know if you need anything else — Gunnarhawk, Feb 25 '21 at 20:22
@Gunnarhawk `to_date('01/01/2020 12:00:00', 'MM/DD/YYYY HH:MI:SS')` is 12 noon. Simplest would be to use a standard [date literal](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1]), though. A test case would help a lot - I don't know what your 'works'/'does not work' examples mean. — William Robertson, Feb 25 '21 at 20:39
Well... yes please. I asked for an insert statement, you provided an output. Obviously this is not a very straightforward issue, so I need *every* possible detail to reproduce... Please provide a reproducible case that you can run yourself. That includes (1) a create table statement (not a desc...), (2) an insert statement for a couple of rows that work and a couple that don't and (3) the selects that work and fail. — Koen Lostrie, Feb 25 '21 at 20:40
@WilliamRobertson The 'works' means that when I add the 'WHERE' clause to my sql statement, the row gets selected. The 'does not work' means that when I add the 'WHERE' clause it does not get selected, yet when I just do a select statement with no 'WHERE' it shows up and displays that information. — Gunnarhawk, Feb 25 '21 at 20:42
@Gunnarhawk what WHERE clause? And examples including inserting values would help in case we are all missing some detail. — William Robertson, Feb 25 '21 at 20:45
I did not create this table, nor have I ever inserted anything into it. That was all done before I got here. I am about to go home for the day but tomorrow I will see about getting the details you need to recreate it @KoenLostrie . I know it is an odd problem, thank you for your help — Gunnarhawk, Feb 25 '21 at 21:05
Is it possible the dates that don't work (to the extent that's clear) have been created as BCE not CE? Can you check and probably rule that out by querying `to_char(date1, 'SYYYY-MM-DD HH24:MI:SS')` and seeing if they show up as `-2000-01-01 00:00:00`? — Alex Poole, Feb 25 '21 at 22:26

score 2 · Answer 1 · answered Feb 25 '21 at 19:22

2

Probably because date1 column contains time component as well.

Try to truncate it:

where trunc(date1) = to_date('01/01/2020', 'mm/dd/yyyy')

answered Feb 25 '21 at 19:22

Littlefoot

131,892
15
35
57

When using trunc(date1) I still get the same result – Gunnarhawk Feb 25 '21 at 19:29
In that case, please, post some sample data so that we'd see what you actually have. – Littlefoot Feb 25 '21 at 19:44
What would you want as far as test data, it is just the first and final days of the month from year 2000 until now – Gunnarhawk Feb 25 '21 at 20:01

Marmite Bomber · Accepted Answer · 2021-10-29T07:18:15.673

2

If you want to select one day of data from a DATE column, that can contain a time 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.

edited Oct 29 '21 at 07:18

answered Feb 25 '21 at 19:31

Marmite Bomber

19,886
4
26
53

How to compare two dates from an oracle database

2 Answers2