Mysql query WHERE date <

Question

I have the 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

The date has a date format, and all dates have the correct Ymd format

but if I use this query, I get no results ... if I change the date to an earlier one, I get the wrong results ... I think it compares the string ... but how can I find the date ??

Answer 1

If jx_commissions.dateis a date field, you can simply fulfill a condition likejx_commissions.date > DATE('2011-01-01')

Answer 2

Try using the STR_TO_DATE function to convert the column to 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