How compare dates that are equal when one has time as well-CodePudding

I'm trying to compare dates and it looks like I'm doing it like examples online, but I only see the rows of dates returned when I do >= for the comparison. When I just use = it doesn't return anything.

This is my query with >= for the date comparison:

select * 
from valhist_tbl --dmh
where 
CRTUPDDT >=  to_date('28-10-21','DD-MM-YY')
and metricvalue  <> 'Device Found'
order by CRTUPDDT asc

I see a lot of this sort of thing returned:

CRTUPDDT                          METRICVALUE
28-OCT-21 12.00.00.069000000 AM   NOT FOUND
28-OCT-21 12.00.00.071000000 AM   NOT FOUND
...

I want it to do this: CRTUPDDT = to_date('28-10-21','DD-MM-YY') but that doesn't return any rows.

I think the comparison as equals and not greater than has to do with the what is returned, but I'm not sure how to fix it. Also, I don't want to see duplicate lines returned, but there's so many that distinct makes it take forever.

This is a link I was looking at: date

CodePudding user response：

Compare on a range over the entire day:

SELECT * 
FROM   valhist_tbl --dmh
WHERE  CRTUPDDT >=  DATE '2021-10-28'
AND    CRTUPDDT <   DATE '2021-10-28'   INTERVAL '1' DAY
AND    metricvalue  <> 'Device Found'
ORDER BY CRTUPDDT asc

You could also compare using TRUNC:

SELECT * 
FROM   valhist_tbl --dmh
WHERE  TRUNC(CRTUPDDT) =  DATE '2021-10-28'
AND    metricvalue  <> 'Device Found'
ORDER BY CRTUPDDT asc

However, if you have an index on CRTUPDDT then it would not be used in this latter query; you would need to have a function-based index on TRUNC(CRTUPDDT) instead.