SQL Oracle table blank when trying to query date data stored as varchar

Question

I have a column called received_dt_key in Varchar in the format DD-MM-YYYY (e.g. 30-07-2021).

I would like to select all from the table for dates between 31-12-2021 and 01-01-2022. I have tried version of the below query and a blank table is the output.

SELECT *
FROM SD_BDAY
WHERE to_char(to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY')) > to_char(to_date('31-12-2021', 'DD-MM-YYYY'))
and to_char(to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY')) < to_char(to_date('01-01-2022', 'DD-MM-YYYY'));

Answer 1

Don't compare dates as strings. Compare them as dates:

SELECT *
FROM SD_BDAY
WHERE to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') > to_date('31-12-2021', 'DD-MM-YYYY')
and   to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') < to_date('01-01-2022', 'DD-MM-YYYY');

If you try to compare them as strings then you are looking for string that is greater than '31-12-2021' and less than '01-01-2022' and the string comparison will look at the first character and see if it can find a match which is greater than '3' and less than '0'; there can never be such a match so it is quite correct that when comparing as strings nothing is returned.

---

As pointed out by @AlexPoole in comments, even if you compare the values as dates (rather than strings) you will still never return a result as finding values that are greater than DATE '2021-12-31' and less than DATE '2022-01-01' would return all dates from 2021-12-31 00:00:01 to 2021-12-31 23:59:59; however, your values will always be converted with a midnight time component and, therefore, will never fall into that range so cannot be returned.

What you probably want is to use >= rather than > and then it would match values on 2021-12-31.

Answer 2

The best thing would be to store calendar dates in date data type column. Why else do you think Oracle designed that data type? This way you may create normal indexes on data data type columns, or, if needed, partition the table by that date column.

Still, if you insist in having the calendar dates stored like that, I think the below should work:

SELECT *
FROM SD_BDAY
WHERE to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') > 
    to_date('31-12-2021', 'DD-MM-YYYY')
  and to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') < 
    to_date('01-01-2022', 'DD-MM-YYYY');

Thus you compare calandar dates with calendar dates, not varchar with varchar, as it results from the code you have written. And what if in the varchar2 column there is somethibng that can't be converted to date? That is why it is best to use the date data type.