compare datetime in oracle

Question

I have a commands as below:

to_char(to_date('req_date','yyyy/mm/dd'),'yyyymmdd) > '20140401'; is success

But after when I have changed year from 2014 to 2015 , have a error format string. As below

to_char(to_date('req_date','yyyy/mm/dd'),'yyyymmdd) > '20150401';

I don't know that is error.Let's fix for me. Thanks for all

These are not actually your commands, are they? At least one quote is missing, and `req_date` is quoted so it's not a valid date. Please copy-paste the actual commands. — RealSkeptic, Apr 19 '15 at 17:32
Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format. See this http://stackoverflow.com/a/29559609/3989608 — Lalit Kumar B, Apr 19 '15 at 17:43

score 0 · Answer 1 · edited May 23 '17 at 10:24

to_char(to_date('req_date','yyyy/mm/dd'),'yyyymmdd) > '20140401';

'req_date' is not a date, it is a string. You don't need single-quotation marks, use the date column as it is.
Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format. See my answer here https://stackoverflow.com/a/29559609/3989608
Most importantly, you are comparing two STRINGS. You need to compare the DATEs. You need to leave the date as it is for DATE calculations. TO_CHAR is for display, and TO_DATE is to convert a string literal into DATE.

So, you need to do the date comparison as:

req_date > to_date('20140401','YYYYMMDD')

1 Answers1