虽然有数据，为什么mysql会返回null？

Question

Check out this:

My SQL query is:

SELECT @rownum := @rownum + 1 AS ID, SKU, SUM(Quantity) FROM orders, (SELECT @rownum := 0) r
WHERE ShipDate BETWEEN 01-05-2013 AND 11-05-2013 
GROUP BY SKU

And in my code, I am doing the following:

$sql = "SELECT @rownum := @rownum + 1 AS ID, SKU, SUM(Quantity) 
FROM orders, (SELECT @rownum := 0) r
WHERE ShipDate BETWEEN {$from_date } AND  {$to_date }
GROUP BY SKU";

I want to select data that falls between two values.

ShipDate is a text field and not a date field. I checked the table, and it holds the data, but MySQL returns null.

Why? What I am doing wrong?

How can I update my PHP code?

This code:

"SELECT @rownum := @rownum + 1 AS ID, SKU, SUM(Quantity) 
FROM orders, (SELECT @rownum := 0) r
WHERE STR_TO_DATE(ShipDate,'%d-%m-%Y') BETWEEN {$from_date } AND  {$to_date }
GROUP BY SKU";

is not working for me.

Please help me.

Answer 1

Use STR_TO_DATE() to convert the text into a date

SELECT @rownum := @rownum + 1 AS ID, 
       SKU, 
       SUM(Quantity) 
FROM orders, (SELECT @rownum := 0) r
WHERE STR_TO_DATE(ShipDate,'%d-%m-%Y') BETWEEN '2013-05-01' AND '2013-05-11'
GROUP BY SKU

and add quotes around the date and rearrange the date to yyyy-mm-dd