sqloracle-database

timestamp in where clause not returning records correctly

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?

Solution

  • 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')