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
Best Answer
I see two problems with your date filter:
You're applying
TO_DATE
on aDATE
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')
:TO_DATE
that has two arguments (DATE
,VARCHAR2
)TO_DATE
function exists, so there is no ambiguity here, the function choosen isTO_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:
In this case you will lose the time component of the date if your
NLS_DATE_FORMAT
isYYYY/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 <= D
B >= C
Your query should look like: