Compare two date columns in SQL Oracle

Question

I have two columns which are both stored as 'MM/DD/YYYYY HH:MM:SS AM (or PM)'. I'm able to use 'TO_CHAR' to change it to 'MM/DD/YYYY' format, but after I get that, I only want the dates where both columns are not equal to each other (after the TO_CHAR). I used '!=' but this doesn't work and it still returns rows when both dates are equal to each other. I basically want the fields to ignore the times, and to only return the row if both dates are different.

Here was the query I was trying to run:

select r.project_name, to_char(r.created_on, 'MM/DD/YYYY'),
       to_char(r.project_opened_on_date, 'MM/DD/YYYY')
from wh_project_repo r
where r.created_on != r.project_opened_on_date

Any help please?

Answer 1

It does not make sense to store dates as a string. But you are doing that.

Because you have strings, there is no need to convert to dates. How about doing:

select r.project_name, to_char(r.created_on, 'MM/DD/YYYY'),
       to_char(r.project_opened_on_date, 'MM/DD/YYYY')
from wh_project_repo r 
where substr(r.created_on, 10) <> substr(r.project_opened_on_date, 10)

The problem with your query, though, is that the manipulations are in the select. This does not affect what the where clause is comparing.

EDIT:

You could also compare the values as dates by doing:

where to_char(r.created_on, 'MM/DD/YYYY') <> to_char(r.project_opened_on_date, 'MM/DD/YYYY')

The issue isn't the string comparisons. The issue is that functions in the select don't affect the where.