CODEWITHSUNDEEP
mysqlsql
HichamEch
HichamEch

Reputation: 715

Sql how to select where day and month between two dates

How to select where day and month between two dates :

for example : 31/December is between 26/December/2016 and 02/January/2017

my query : 

select table2.*
from table1,
     table2
where table2.day between DAYOFMONTH(table1.date_start) and DAYOFMONTH(table1.date_end)
  and table2.month between MONTH(table1.date_start) and MONTH(table1.date_end)

table1 (reservation) :

-id -date_start (date) -date_end (date)

1 | 2016-12-26 | 2017-01-02

table2: (recipe)

id | name | day (int) | month (int)

1 | xxx | 31 | 12

Upvotes: 1

Views: 2897

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

It looks like you are using MySQL. Rather than trying to check the months and days separately, instead just use BETWEEN with the two points whose range you wish to capture.

SELECT t1.*
FROM table1 t1
INNER JOIN table2 t2
    ON t1.some_col = t2.some_col
WHERE day_col BETWEEN '2016-12-26' AND '2017-01-02'

Note that I also added a join condition to your query, without which you would be doing a cross join, most likely not what you intend.

Upvotes: 2

Related Questions