BETWEEN months query in MySQL

BETWEEN months query in MySQL

I am trying to get rows witch has a season start date and season end date in current date. But I have problem with periods between months in winter. For example winter starts 01.11 and end at 28.02 (I don't care about 27 or 28)

When I try to get products in winter like below query

SELECT *  FROM products P  LEFT JOIN seasons S ON P.s_id = S.id  WHERE MONTH(CURDATE()) BETWEENS MONTH(S.startdate) and MONTH(S.enddate)  

I get nothing

The table seasons has one row with below value

id = 1  Description = Winter  startdate = 2013-11-01  enddate = 2014-02-28  

!IMPORTANT I don;t care about year

Can anyone help? thanks

Answer by Filipe Silva for BETWEEN months query in MySQL

You can achieve this with a CASE:

SELECT *  FROM products P  LEFT JOIN seasons S ON P.s_id = S.id  WHERE CASE   WHEN MONTH(S.startdate) > MONTH(S.enddate)    AND (MONTH(CURDATE()) > MONTH(S.startdate)         OR MONTH(CURDATE()) < MONTH(S.enddate))    THEN 1  WHEN MONTH(S.startdate) <= MONTH(S.enddate)    AND MONTH(CURDATE()) BETWEEN MONTH(S.startdate) AND MONTH(S.enddate)    THEN 1  ELSE 0  END  

This assumes that whenever MONTH(startdate) > MONTH(enddate) the year has changed. Will return true in that case whenever MONTH(curdate()) is bigger than MONTH(startdate) OR is smaller than MONTH(enddate).

In the case when MONTH(startdate) <= MONTH(enddate) it just validates if it is between them.

sqlfiddle demo

Answer by Marc B for BETWEEN months query in MySQL

Why would you get anything? You're doing the equivalent of

WHERE 5 BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd'  

You're doing a literal apples/oranges comparison. Perhaps you want

WHERE MONTH(CURDATE()) BETWEEN MONTH(S.startdate) AND MONTH(S.enddate)  

instead, so you're doing apples to apples.

Answer by D Mac for BETWEEN months query in MySQL

Between requires the values be in order - your start date is AFTER your end date so you'll always get nothing.

Additionally, you are comparing months to dates; since the date has the year in it you can't ignore the year that way.

You have to compare the day in year to ignore the year. If DAYOFYEAR(curdate()) is between DAYOFYEAR(end date) and DAYOFYEAR(start date) that will work if both end data and start date are in the same year. You'll need to get a bit fancier if they are in different years, but that should be obvious.

Also, you probably have to think through what "not caring about year" really means. If you're looking for dates between March and September, that will be the reverse of looking for dates between September and March. What you want will alter how you program it.

Answer by Ambitos for BETWEEN months query in MySQL

I found solution.

SELECT *    FROM products P  LEFT JOIN seasons S ON P.s_id = S.id    WHERE     IF(   ( CURDATE() BETWEEN STR_TO_DATE( CONCAT( YEAR(CURDATE()),'-',MONTH(S.startdate),'-',DAY(S.startdate)) , '%Y-%m-%d')  AND STR_TO_DATE( CONCAT( IF( MONTH(S.`enddate`) < MONTH(S.`startdate`) ,YEAR(CURDATE())+1,YEAR(CURDATE()) ),'-',MONTH(s.`enddate`),'-',DAY(S.`enddate`)) , '%Y-%m-%d')  ),    "IN Season",    IF( P.`s_id` IS NOT NULL AND P.`s_id` != "","Out OF Season","" )  ) = "IN Season"  

I have 2 rows
1. Startdate: 2013-11-01 EndDate: 2014-02-28 (Winter)
2. StartDate: 2013-06-01 ENDDate: 2013-08-31 (Summer)

It works perfect.

Answer by AndySavage for BETWEEN months query in MySQL

I don't like the strange converting to strings and parsing solutions. You can do this with a little math...

SELECT *  FROM products P  LEFT JOIN seasons S ON P.s_id = S.id  WHERE ((DAYOFYEAR(CurDate()) - DAYOFYEAR(S.startdate)) + 365) % 365 BETWEEN 0 AND       (((DAYOFYEAR(S.enddate) + 365) - DAYOFYEAR(S.startdate))) % 365  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72