0

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

Michele
  • 3,617
  • 12
  • 47
  • 81
  • 3
    Try using `trunc` on that column that way it will exclude the time part of the date . like `trunc(CRTUPDDT)` – Miguel Nov 17 '21 at 20:25
  • 1
    Does this answer your question? [Comparing with date in Oracle sql](https://stackoverflow.com/questions/15970277/comparing-with-date-in-oracle-sql) – Koen Lostrie Nov 17 '21 at 20:27

1 Answers1

1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117