Compare dates problems I created a table with the following columns:

CREATE TABLE INDICADOR_DADOS
(

  ID       VARCHAR2(10 CHAR)   NOT NULL,
  DATE_START       DATE        NOT NULL,
  DATA_END         DATE        NOT NULL,
)


Now wanted before adding, checks if there are dates between which intend to add the following query:

 select COUNT(*) as count from indicador_dados
        where id='23' and TO_DATE(data_inicio,'yyyy/mm/dd') 
            between to_date('2013/05/15','yyyy/mm/dd')
            and to_date('2013/05/16','yyyy/mm/dd')


The problem is that this query is not to be returned to 1, and because they must have in the database 05/03/2013 (date_start) and 17/05/2013(date_end). Someone can help me?

maybe duplicate: oracle-datetime-in-where-clause but not it helped me to resolve
I see two problems with your date filter:


You're applying TO_DATE on a DATE datatype. It is not only superfluous, but it actually causes unexpected behaviour. 

Here's how Oracle analyses the expression TO_DATE(data_inicio,'yyyy/mm/dd'):


Oracle searches for a function named TO_DATE that has two arguments (DATE, VARCHAR2)
Since no such function exists, Oracle looks for related functions that are accessible with implicit conversions.
(Un)fortunately, a single TO_DATE function exists, so there is no ambiguity here, the function choosen is TO_DATE(VARCHAR2, VARCHAR2).
Your date is converted to a VARCHAR2 with the current session date format, which means that the behaviour is unpredictable. The expression is equivalent to:

TO_DATE(TO_CHAR(data_inicio, 'current NLS_DATE_FORMAT'), 'yyyy/mm/dd')


In this case you will lose the time component of the date if your NLS_DATE_FORMAT is YYYY/MM/DD.


You should never rely on implicit conversions because the behaviour is context dependent.
Your interval filter is wrong: two intervals [A,B] and [C,D] will intersect if and only if:


A &lt;= D
and B &gt;= C



Your query should look like:

SELECT COUNT(*) AS COUNT
  FROM indicador_dados
 WHERE id = '23'
   AND date_start  &lt;= to_date('2013/05/16', 'yyyy/mm/dd')
   AND date_end &gt;= to_date('2013/05/15', 'yyyy/mm/dd')