mysql select dates between

Question

I have a single mysql table called prices storing dates from and to like so:

id    from          to              price
1     2013-11-25    2014-01-08      55

the user selects a month and a year and i want it to show all results that either are from or to that month so if the user selected either november 2013, december 2013 or january 2014 it would include the above row.

id    from          to              price
2     2013-10-25    2013-11-07      100

and if they selected either october or november 2013 it would show the above row because it starts in october and ends november.

i have tried

SELECT * FROM `prices`
WHERE MONTH(prices.from)<={$_SESSION['month']}
AND MONTH(prices.to)>={$_SESSION['month']}
AND YEAR(prices.to)={$_SESSION['year']}
ORDER BY prices.from ASC

which doesn't work i'm not sure how this can be done

I'm pretty sure that greater than and less than don't work in mysql queries, you can use WHERE xxx BETWEEN xxx AND xxx — Michael St Clair
– Michael St Clair, Commented Nov 9, 2013 at 17:54
What is 'doesn't work'? BTW, how do you want to deal with cases where one selected month returns 2 rows of prices? — hgoebl
– hgoebl, Commented Nov 9, 2013 at 17:56
I'm not sure how i can use BETWEEN because the user selects one month and year not a range of months — user1869566
– user1869566, Commented Nov 9, 2013 at 18:22

Filipe Silva · Accepted Answer · 2013-11-09 18:24:50Z

1

You could use STR_TO_DATE to create a date from the month and year the user selects and then use DATE_FORMAT to put your table's date column in YYYY-MM format and compare both with the YYYY-MM the user inserted.

I'm not fluent in php, but here's an example in sql (you can use any day in the str_to_date, since you will ignore it):

SELECT * FROM `prices`
WHERE DATE_FORMAT(prices.`from`,"%Y-%m") <= 
   DATE_FORMAT(STR_TO_DATE('01,10,2013','%d,%m,%Y'),"%Y-%m")
AND DATE_FORMAT(prices.`to`,"%Y-%m") >= 
   DATE_FORMAT(STR_TO_DATE('01,10,2013','%d,%m,%Y'),"%Y-%m")
ORDER BY prices.`from` ASC

sqlfiddle demo

answered Nov 9, 2013 at 18:24

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

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

Comments

mortymacs · Accepted Answer · 2013-11-09 17:58:44Z

0

Try to use "BETWEEN" instead. example:

SELECT * FROM `prices` WHERE MONTH(`from`) BETWEEN '10' AND '12' AND YEAR(`to`)='2013'

answered Nov 9, 2013 at 17:58

mortymacs

3,7865 gold badges34 silver badges58 bronze badges

Comments

Shaun · Accepted Answer · 2013-11-09 18:37:52Z

0

Follow this method:

SELECT * from prices
WHERE str_to_date('{$_SESSION['month']}-{$_SESSION['year']}','%b-%Y') BETWEEN date(prices.from) AND date(prices.to)

You might just want to use the correct format in the str_to_date, currently it converts the following format = Oct-2013

Check this for other formats: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

edited Nov 9, 2013 at 18:37

answered Nov 9, 2013 at 18:27

Shaun

5591 gold badge3 silver badges18 bronze badges

Collectives™ on Stack Overflow

mysql select dates between

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related