Auj Auj - 1 year ago 101
MySQL Question

Retrieve data between two dates in mysql

I am having trouble with MySQL query, I have a "products" table. In this table I have a "product_expiry" column. I am trying to create a form which will retrieve all products which have "product_expiry" dates between two dates.

For example I want to get products which have "product_expiry" date from "04/2016" to "09/2016".

For this I decided to go with this query.

SELECT * FROM `products` WHERE MONTH(product_expiry) >= "4" AND MONTH(product_expiry) <= "9" AND YEAR(product_expiry) = "2016"

It returned 0 products though I have products between these dates.
I thought the problem is with the format of the date.

Then I tried

SELECT * FROM `products` WHERE MONTH(STR_TO_DATE(`product_expiry`,'%d,%m,%Y')) >= "4" AND MONTH(STR_TO_DATE(`product_expiry`,'%d,%m,%Y')) <= "9" AND YEAR(STR_TO_DATE(`product_expiry`,'%d,%m,%Y')) = "2016"

But it is again returning 0 products.

"product_expiry" is varchar not a date column
and Format of the date is day/month/year

Answer Source

Try with the below query. I replaced the '%d,%m,%Y' to '%d/%m/%Y' in the WHERE clause:

FROM `products`
WHERE MONTH(STR_TO_DATE(`product_expiry`, '%d/%m/%Y')) >= 4
    AND MONTH(STR_TO_DATE(`product_expiry`, '%d/%m/%Y')) <= 9
    AND YEAR(STR_TO_DATE(`product_expiry`, '%d/%m/%Y')) = 2016

Please find the Working Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download