SQL To get count for a particular date range

Question

I want to count incomplete orders for particular date range

My query:

select COUNT(ORDER_ID)
from Atable
where
  ORDER_TYPE='INCOMPLETE' and   
  DATE BETWEEN '31-OCT-14' AND '07-NOV-14'

But DATE column is TimeStamp.

How can I use a wild-card and between together?

Answer 1

When you use a timestamp column with string literals like that, the timestamp is implicitly cast to varchar, and the comparison is performed lexicographically - which, of course, is not the behavior you want. You can use the to_timestamp to explicitly force the cast in the direction you want:

SELECT COUNT(ORDER_ID) 
FROM   atable 
WHERE  order_type = 'INCOMPLETE'  AND
       "DATE" BETWEEN TO_TIMESTAMP('31-OCT-14', 'DD-MON-YY') AND 
                      TO_TIMESTAMP('07-NOV-14', 'DD-MON-YY')

EDIT:
According to the clarification in the comments, you need a group by clause to the results per day:

SELECT   TO_TIMESTAMP("DATE", 'DD-MON-YY'), COUNT(ORDER_ID) 
FROM     atable 
WHERE    order_type = 'INCOMPLETE'  AND
         "DATE" BETWEEN TO_TIMESTAMP('31-OCT-14', 'DD-MON-YY') AND 
                        TO_TIMESTAMP('07-NOV-14', 'DD-MON-YY')
GROUP BY TO_TIMESTAMP("DATE", 'DD-MON-YY')

Answer 2

Try with timestamp as below:

SELECT COUNT(ORDER_ID) 
FROM Atable 
WHERE ORDER_TYPE='INCOMPLETE' 
AND   DATE BETWEEN 2014-10-31 00:00:01 AND 2014-11-07 23:59:59