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!

Solution 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

Solution 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.

Comments

[Mark Sholund]

what database are you using?

[Brian M]

We are using an Oracle DB

[Brian M]

OK, I tried that. Now I get nothing for a result set. This is my exact query select TO_CHAR(actstartdate, 'DD-MON-YYYY') from lawgen9.queuedjob where username ='sspradli' and jobname = 'MA1802300' and TO_CHAR(actstartdate, 'YYYY-MM-DD') > '2015-10-01' and rownum < 100 order by actstartdate;

[rp372]

I am not sure why you aren't getting results, but you should look at this post for a complete explanation: stackoverflow.com/questions/10178292/…

[Brian M]

Thank you, I will look at it.,

[Brian M]

I tried different variations of what they say but none seem to work.