mysql query problem WHERE date <

Question

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 do 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??

enter image description here

Looks okay - if date is a date field, this will do a date comparison. Can you show what kind of wrong results you get? — Pekka
– Pekka, Commented Jan 25, 2011 at 22:53
if i use date from 2010-09-01 i get reuslt also from 2010-08-xx maybe even lower... — Alexander_F
– Alexander_F, Commented Jan 25, 2011 at 23:09
the problem was LIMIT 1,20... i had only one result.. so i have to use 0, 20... — Alexander_F
– Alexander_F, Commented Jan 28, 2011 at 15:23

Aaron Hathaway · Accepted Answer · 2011-01-25 23:22:30Z

2

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

answered Jan 25, 2011 at 23:22

Aaron Hathaway

4,3832 gold badges21 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aaron Hathaway Over a year ago

I just did it in my DB. What exactly are you trying? I think you might be doing something wrong.

Aaron Hathaway Over a year ago

Can you do a non identifiable export of your database and show us on pastebin.com?

Alexander_F Over a year ago

pastebin.com/hHBt46SC here is this table... but no Data... there some sensible data :) i hope you can help me...

Aaron Hathaway Over a year ago

Checkity check it. I made this pastebin.com/1twyJhVB from your structure and insesrted four items with dates from the 26th-29th. If you run a query like "SELECT * FROM jx_commissions WHERE date > DATE('2011-01-27')" you only get the 28th and 29th. I thought it was maybe because you had "date" and not "date" but my tool doesn't care. Let me know if this helps or not.

Joe Stefanelli · Accepted Answer · 2011-01-25 22:52:41Z

0

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

answered Jan 25, 2011 at 22:52

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Collectives™ on Stack Overflow

mysql query problem WHERE date <

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related