CODEWITHSUNDEEP
mysqlsql-serverpostgresql
koe
koe

Reputation: 746

php sql query select between month and year

I want select SQL query in where condition between month and year. in my database have field name months and years for store produce input of month and year. now I want search produce input in December,2015 to august 2016 This is my sql 

SELECT * FROM tbl_produce p WHERE p.status =0 AND ((p.months >='12' 
AND p.years>='2015') AND (p.months <='8' AND p.years<='2016)) ;

but result return NULL, I known my query not correct but how to correct it ?

Upvotes: 0

Views: 2842

Answers (2)

Ross Hardin
Ross Hardin

Reputation: 1

Your SQL query is looking for result where p.months is equal to both 12 and 8, and where p.years is equal to both 2015 and 2016.

To achieve the result you are looking for, replace the AND between the two sets of parenthesis with OR, to look like so:

SELECT * FROM tbl_produce p WHERE p.status =0 AND ((p.months >='12' AND p.years = '2015') OR (p.months <='8' AND p.years = '2016));

If I had access to your table structure I would run the test myself to confirm, but I do believe this to be the correct syntax for the result you are looking for.

Upvotes: 0

SIDU
SIDU

Reputation: 2278

Why not design your date column as date ?

If you want to keep your current design, try this:

SELECT * 
FROM tbl_produce
WHERE status = 0 
AND concat(years, if(months > 9, months, concat('0', months))) 
  BETWEEN '201512' AND '201608'

OR

SELECT * 
FROM tbl_produce
WHERE status = 0 
AND (
  (years = 2015 AND months = 12) 
  OR 
  (years = 2016 AND months < 9)
)

Upvotes: 0

Related Questions