Oracle – Compare Two Dates Using CASE in WHERE Clause

Thanks for posting the sample data. It would also be helpful to describe in words and with actual output what you want to be returned by your query.

I'm guessing that you want something like

SQL> ed
Wrote file afiedt.buf

  1  WITH T1 AS
  2  (
  3  SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  4  SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  5  SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
  6  SELECT 4 AS SEQ, 'SOMETHING 2' AS SOME_TYPE FROM DUAL UNION ALL
  7  SELECT 5 AS SEQ, 'SOMETHING 3' AS SOME_TYPE FROM DUAL UNION ALL
  8  select 6 as seq, 'SOMETHING 4' AS SOME_type from dual
  9  )
 10  , T2 AS
 11  (
 12  SELECT 'A' AS COMPARE_TYPE FROM DUAL UNION ALL
 13  SELECT 'B' AS COMPARE_type FROM DUAL
 14  )
 15  SELECT T2.*, T1.*
 16    FROM T1, T2
 17   WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
 18                    T1.SOME_TYPE LIKE 'NOTHING%'
 19                 THEN 1
 20               WHEN T2.COMPARE_TYPE != 'A' AND
 21                    T1.SOME_TYPE NOT LIKE 'NOTHING%'
 22                 THEN 1
 23               ELSE 0
 24*           END) = 1
SQL> / 

C        SEQ SOME_TYPE
- ---------- -----------
A          1 NOTHING 1
A          2 NOTHING 2
B          3 SOMETHING 1
B          4 SOMETHING 2
B          5 SOMETHING 3
B          6 SOMETHING 4

6 rows selected.

But I'm making a lot of guesses about what your code is supposed to mean.

That this appears to be identical to a question someone asked in the OTN forums. My answer is the same in both places.

I see two problems with your date filter:

1. 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.

2. Your interval filter is wrong: two intervals [A,B] and [C,D] will intersect if and only if:

   • A <= D
   • and B >= C

Your query should look like:

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