0
SELECT DECODE((SELECT COUNT(*) FROM dual WHERE TO_CHAR(SYSDATE) BETWEEN TO_CHAR    ('01/Jan/2013') AND TO_CHAR('01/Jan/2020')),1,'Yes','No') FROM dual;
Which returns - NO

SELECT DECODE((SELECT COUNT(*) FROM dual where to_date(SYSDATE) BETWEEN to_date    ('01/Jan/2013') AND to_date('01/Jan/2020')),1,'Yes','No') FROM dual;
Which returns - YES

From (01/jan/2013) to (10/Jan/2013) - returns No
 SELECT DECODE((SELECT COUNT(*) FROM dual WHERE TO_CHAR(SYSDATE) BETWEEN TO_CHAR        ('01/Jan/2013') AND TO_CHAR('01/Jan/2020')),1,'Yes','No') FROM dual;
to 
SELECT DECODE((SELECT COUNT(*) FROM dual WHERE TO_CHAR(SYSDATE) BETWEEN TO_CHAR        ('01/Jan/2013') AND TO_CHAR('10/Jan/2020')),1,'Yes','No') FROM dual;
 Returns - No, 

But from 11/jan/2020 its returns Yes
SELECT DECODE((SELECT COUNT(*) FROM dual WHERE TO_CHAR(SYSDATE) BETWEEN TO_CHAR    ('01/Jan/2013') AND TO_CHAR('11/Jan/2020')),1,'Yes','No') FROM dual;
Which returns - YES

I am not able to understand why oracle returns like this, Please get me clear on this. Thanks.

Naveen
  • 3
  • 3
  • Yes, marked for close as dupe. In brief, compare dates to other dates, and strings to other strings. '01/jan/2013' is a string, to_date('01/jan/2013','DD/MM/YYYY') is a date, sysdate is a date. http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#SQLRF0021 – David Aldridge Dec 11 '13 at 08:12

1 Answers1

1

I'm not sure this is quite a duplicate; although the linked question covers similar ground and the advice to compare strings to strings and dates to dates is certainly valid, you're already doing that, and I'm not sure it will necessarily help you understand what you're seeing here.

The key is to look at what the actual string values are when you do the string comparisons. I've assumed your NLS_DATE_FORMAT is DD/Mon/YYYY as that's the way you're writing fixed dates, but it would work with the default DD-MON-RR as well. I'm also ignoring potential complications from NLS_SORT and other settings.

select to_char(sysdate), to_char('01/Jan/2013'), to_char('01/Jan/2020') from dual;

TO_CHAR(SYSDATE)     TO_CHAR('01/JAN/2013') TO_CHAR('01/JAN/2020')
-------------------- ---------------------- ----------------------
11/Dec/2013          01/Jan/2013            01/Jan/2020

Firstly, the to_char() of the fixed strings doesn't do anything, you're converting a string to a string. But think about what happens when you plug those values into your where clause;

select decode(count(*), 1, 'Yes', 'No') from dual
where to_char(sysdate) between to_char('01/Jan/2013') and to_char('01/Jan/2020');

becomes

select decode(count(*), 1, 'Yes', 'No') from dual
where '11/Dec/2013' between '01/Jan/2013' and '01/Jan/2020';

This is purely a string comparison; although they still represent dates to you, to the SQL engine they are just strings. The comparison fails on the first character - the first 1 in 11/Dec/2013 is compared with the 0 in the two other strings, and '1' is not between '0' and '0'. (Simplifying a bit).

When you compare against the 10th,

select decode(count(*), 1, 'Yes', 'No') from dual
where to_char(sysdate) between to_char('01/Jan/2013') and to_char('10/Jan/2020');

becomes:

select decode(count(*), 1, 'Yes', 'No') from dual
where '11/Dec/2013' between '01/Jan/2013' and '10/Jan/2020';

which fails because '11' is not between '01' and '10'.

In your last query,

select decode(count(*), 1, 'Yes', 'No') from dual
where to_char(sysdate) between to_char('01/Jan/2013') and to_char('11/Jan/2020');

becomes:

select decode(count(*), 1, 'Yes', 'No') from dual
where '11/Dec/2013' between '01/Jan/2013' and '11/Jan/2020';

and (simplifying a bit again) the comparison succeeds because '11/D' is, as a string, between '01/J' and '11/J'.

Using a different format mask would allow this to work consistently, but the only safe and reliable way to to this is to compare everything as dates, and to explicitly state the date format model in the to_date() calls for your fixed values:

select decode(count(*), 1, 'Yes', 'No') from dual
where sysdate between to_date('01/Jan/2013', 'DD/Mon/YYYY')
  and to_date('01/Jan/2020', 'DD/Mon/YYYY');

And don't do to_date(sysdate); as the linked question mentioned that does an implicit conversion to date to string, which is pointless at best, but can introduce problems if a format model is specified. If you're trying to remove the time portion of the current date you can use trunc(sysdate) rather than converting to a string and back.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318