I'm working with an Oracle database. I have a timestamp that I am trying to filter my dataset on.
where to_char(d.status_DATE, 'MM/DD/YYYY') > '01/01/2020'
The actual timestamp is like this 20-NOV-06 11: 00: 00
,
to_char part returns 11/20/2026
.
However this does not work and is still returning all records.
How would I make this work to filter my dataset?
Don't compare dates as strings (especially not as strings formatted by month-day-year).
Compare the dates as dates against a date literal:
SELECT *
FROM table_name d
WHERE d.status_date > DATE '2020-01-01'
or convert your string to a date using TO_DATE
(rather than dates to strings):
SELECT *
FROM table_name d
WHERE d.status_date > TO_DATE('01/01/2020', 'MM/DD/YYYY')