alilland alilland - 6 months ago 16
MySQL Question

MySQL Between Statement and >= <= > < not working

i cant get my between statement to work, I can get

AND EffectiveDate >= '2015-06-01'
to work, as soon as i add another date condition it breaks. I have tried using
CAST('2015-06-01' AS DATE)
with no success, i have also tried using
'2015-06-01 00:00:00'
variations of dates as well with no success.

SELECT
ID,
Division,
EffectiveDate,
PM,
case Status
when 0 then 'Dead'
when 1 then 'Active'
when 2 then 'Job'
when 3 then 'Pending'
when 4 then 'Sales Lead'
when 5 then 'Budget'
when 6 then 'Change Order'
end as Status,
Name,
Address,
ProjectType,
sellPrice
FROM intranet.t_bidinfo
WHERE Division = 'TI'
AND Status = 2 OR Status = 6
AND EffectiveDate BETWEEN '2015-06-01' AND '2015-06-30'
ORDER BY EffectiveDate ASC
;

Answer

One problem with the query is that OR has lower precedence than AND: the condition on EffectiveDate does not apply to rows that have Division = 'TI' and Status = 2.

You probably want to write

AND (Status = 2 OR Status = 6)

or, equivalently,

AND Status IN (2, 6)
Comments