mysql query problem WHERE date <

i have following query

SELECT *, count(jx_commissions.commission_amount) AS summe 
FROM jx_members 
INNER JOIN jx_commissions ON jx_commissions.mid = jx_members.mid 
WHERE jx_commissions.date > '2011-01-01'
GROUP BY jx_commissions.mid 
ORDER BY summe DESC 
LIMIT 1, 20

field Date have a date format and all dates have the right format Y-m-d

but if i use this query i d开发者_开发百科o not get any results... if i change date to a nother one, i get wrong results... i think he compare a string... but how can i search for a date??

If jx_commissions.date is a date field you can just do a conditional like jx_commissions.date > DATE('2011-01-01')

Try using the STR_TO_DATE function to convert the column into a true date.

SELECT *, count(jx_commissions.commission_amount) AS summe 
FROM jx_members 
INNER JOIN jx_commissions ON jx_commissions.mid = jx_members.mid 
WHERE STR_TO_DATE(jx_commissions.date, '%Y-%m-%d') > '2011-01-01'
GROUP BY jx_commissions.mid 
ORDER BY summe DESC 
LIMIT 1, 20