How do I correctly compare dates using to_char?

Question

I have a query similar to :

SELECT STUFF 
FROM TABLENAME
WHERE TO_CHAR(STARTDATE, 'DD-MON-YYYY') > '01-OCT-2015'

My result set contains STARTDATEs that all are less than '01-OCT-2015'

What am I doing wrong? Thanks so much!

Answer 1

it is more recommended to compare dates in this case and not strings

if you compare strings, the query will have to convert all dates in the table relevant column into strings instead of converting a single string into date.

And this way dates are compared correctly for sure regardless of the printing format

SELECT STUFF 
FROM TABLENAME
WHERE STARTDATE > to_date('01-OCT-2015 00:00:00' , 'DD-MON-YYYY HH24:MI:SS')

And you can try the query:

select to_date('01-OCT-2015 00:00:00' , 'DD-MON-YYYY HH24:MI:SS') from dual;

to check if the result is as expected before continuing with main query

Answer 2

This will compare the dates as strings, which will be ordered alphabetically. If you want to compare them as strings, you should use the format 'YYYY-MM-DD' which will correctly order alphabetically. Note that 'MM' is month as a zero-padded integer, not as the month abbreviation.