CODEWITHSUNDEEP
oracle-databaseto-char
Brian M
Brian M

Reputation: 31

How do I correctly compare dates using to_char?

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!

Upvotes: 3

Views: 15347

Answers (4)

rajesh parab
rajesh parab

Reputation: 1

You need to do do_char first and then do_date again.

select creation_date from my_table  
where
to_date(to_char(creation_date,'MM-DD-YYYY'),'MM-DD-YYYY')
>=  to_date(to_char((sysdate - 365),'MM-DD-YYYY'),'MM-DD-YYYY')
  order by creation_date desc

You will get all data one year from today. If you only put to_char, it will give the correct result.

Upvotes: 0

Vu Luu
Vu Luu

Reputation: 790

SELECT STUFF
FROM TABLENAME
WHERE to_date( STARTDATE, 'DD-Mon-YYYY' ) > to_date( '01-Oct-2015', 'DD-Mon-YYYY' );

Upvotes: -2

nabeel
nabeel

Reputation: 319

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

Upvotes: 3

rp372
rp372

Reputation: 345

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.

Upvotes: 1

Related Questions