Getting Incorrect results for selecting values between 2 dates

I am trying to run the below query to select some results between 2 dates but I am getting results as the condition doesn’t exist. So what is wrong here?

select OPEN_TIME, STATUS 
from PROBSUMMARY 
where trunc (open_time) >= '01-01-2020' 
  and trunc (open_time) < '01-01-2021'

Answer

You are comparing dates with strings; the dates are converted to strings to do that comparison. From the documentation:

During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.

When your dates are implicitly converted to strings, they all fall within that range of string values, based on the character value comparison rules.

The above isn’t true of course; this supersedes it:

When comparing a character value with a DATE value, Oracle converts the character data to DATE.

So something odd is happening in that implicit conversion, which will depend on your NLS_DATE_FOPRMAT setting. Anyway, the following advice still stands…

Compare dates with dates instead, either converting your strings with to_date() or with date literals:

where open_time >= date '2020-01-01' and open_time < date '2021-01-01'

You don’t need to truncate the dates back to midnight; which will help with being able to use an index, if appropriate.