searching between dates in MYSQL in this format 03/17/10.11:22:45

Question

I have a script that automatically populates a mysql database with data every hour. It populates the date field like 03/17/10.12:34:11 and so on.

I'm working on pulling data based on 1 day at a time from a search script.

If i use

SELECT * 
  FROM call_logs 
 WHERE call_initiated between '03/17/10.12:00:00' and '03/17/10.13:00:00'

it works, but when I try to add the rest of the search params, it ignores the call_initiated field.

SELECT * 
  FROM call_logs 
 WHERE  caller_dn = '2x9xxx0000' OR called_dn = '2x9xxx0000' 
   AND call_initiated between '03/17/10.12:00:00' and '03/17/10.13:00:00'

^-- I x'd out a couple of the numbers. I've also tried without the between function, and used >= <= to pull the records, but have the same results. Im sure its an oversight, thanks in advance.

Answer 1

• try using parentheses around your OR statement ... where (caller_dn='2x9xxx0000' OR called_dn='2x9xxx0000') AND call_initiated between '03/17/10.12:00:00' and '03/17/10.13:00:00'

• use the IN operator instead of OR ... where caller_dn IN('2x9xxx0000','2y9yyyy000') AND call_initiated between '03/17/10.12:00:00' and '03/17/10.13:00:00'

Answer 2

The OR statement is more than likely the issue, since OR evaluates the left side, sees that its true, it doesn't care what is on the right site, because as long as one of the statements is true, it considiers the entire statement to be true.

Read up on the OR operator at the MYSQL page

Your Statement should look like

SELECT * FROM `call_logs` WHERE (caller_dn='2x9xxx0000' OR called_dn='2x9xxx0000') AND call_initiated BETWEEN '03/17/10.12:00:00' and '03/17/10.13:00:00';