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