Geek asks and answers

BETWEEN AND OR Select

How can I make a filter in a date range

Select * from  XXX where date between DATE1 AND DATE2 OR Between DATE3 AND DATE4

(Copied from a post posted as a response)

Here is my condition

WHERE  ( items_count != '0' )
       AND ( main_table.is_active = '1' )
       AND ( main_table.store_id IN ( '0', '1' ) )
       AND ( main_table.updated_at BETWEEN
             '2011-03-04 16:52:19' AND '2011-03-05 16:52:19'
           )
        OR ( main_table.updated_at BETWEEN
             '2011-03-13 16:52:19' AND '2011-03-14 16:52:19'
           )
LIMIT  0, 30

The first condition is never used

+3
source share
4 answers
Select * from  XXX 
  where (date between DATE1 AND DATE2) 
        OR 
        (date between DATE3 AND DATE4)

EDIT:

Try the following:

WHERE items_count != '0' AND 
      main_table.is_active = '1' AND 
      main_table.store_id IN ('0', '1') AND 
      (  main_table.updated_at BETWEEN '2011-03-04 16:52:19' AND '2011-03-05 16:52:19' 
         OR 
         main_table.updated_at BETWEEN '2011-03-13 16:52:19' AND '2011-03-14 16:52:19'
      )
+3
source

Treat them as independent conditions:

select * from xxx where (date between date1 and date2) or (date between date3 and date4);
+1
source

Like this:

 select * from XXX 
 where (date between DATE1 AND DATE2) 
    or (date between DATE3 AND DATE4)
+1
source

A bit of OCD always helps with SQL ...

You do not need all the brackets that you have, but OR does not work as you expect.

.... 
   WHERE 
    items_count != '0'        
    AND 
    main_table.is_active = '1'        
    AND 
    main_table.store_id IN ('0', '1')
    AND (
      main_table.updated_at
      BETWEEN '2011-03-04 16:52:19'
      AND '2011-03-05 16:52:19'
      OR 
      main_table.updated_at
      BETWEEN '2011-03-13 16:52:19'
      AND '2011-03-14 16:52:19'
    )
    LIMIT 0 , 30
+1
source

More articles:


All Articles