Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What dates are you actually looking for in the resultset though? Please edit your answer and explain in plan English. In the above example, do you want '2013/05/16 23:59:59' to be included, for example? –  FreshPhilOfSO May 2 '13 at 10:27
    
Already edited. I apologize for my poor english. I just want to compare dates and not time –  zmki May 2 '13 at 10:37
    
Is this table for a reservation system (like for rooms in a hotel)? –  ypercube May 2 '13 at 10:43
    
Yes, more or less. It is to record how many calls were made ​​between certain date –  zmki May 2 '13 at 10:46
    
Sample data and expected result would help. You may need to use this condition WHERE id='23' AND date_start < DATE '2013-05-06' AND DATE '2013-05-15' < date_end –  ypercube May 2 '13 at 10:52
show 5 more comments

1 Answer

up vote 7 down vote accepted

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')
share|improve this answer
    
Very thz for your help. You just solve my problem. –  zmki May 2 '13 at 13:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.