CODEWITHSUNDEEP
phpmysqlmysqli
Jeff
Jeff

Reputation: 1017

Mysql query select all dates (day & month only) within next 45 days

I need to select all dates (day & month) within the next 45 days (regardless of year). The below query has stopped working. Like literally started returning no rows when there is definitely data there.

Is there another way to do this query? Happy to check records within a php loop if needed.

Essentially I need to show all records within the next 45 days, even if the year has lapsed.

SELECT
   p.*,
   c.company 
FROM
   products p 
   LEFT JOIN
      customers c 
      ON c.id = p.id 
WHERE
   DATE_FORMAT(p.service_date, '%m-%d') >= DATE_FORMAT(CURDATE(), '%m-%d') 
   AND DATE_FORMAT(p.service_date, '%m-%d') <= DATE_FORMAT((CURDATE() + INTERVAL 45 DAY), '%m-%d') 
   AND c.email_service = 1 
ORDER BY
   p.service_date ASC

Upvotes: 0

Views: 1080

Answers (3)

Nick
Nick

Reputation: 147146

The best way to solve this problem is to use DAYOFYEAR. The difficult part is dealing with the case when you are within 45 days of the end of the year, in which case you have to split the check to see if the date is between now and the end of the year or the beginning of the year and now + 45 days; otherwise you just check if the day of the year is between now and now + 45 days. Try replacing your condition with this:

CASE WHEN DAYOFYEAR('2018-12-31')-DAYOFYEAR(NOW()) < 45 THEN
         DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR('2018-12-31') OR
         DAYOFYEAR(p.service_date) BETWEEN 1 AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
     ELSE
         DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
     END

Your full query should then look like this:

SELECT
   p.*,
   c.company 
FROM
   products p 
   LEFT JOIN
      customers c 
      ON c.id = p.id 
WHERE
    (CASE WHEN DAYOFYEAR('2018-12-31')-DAYOFYEAR(NOW()) < 45 THEN
              DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR('2018-12-31') OR
              DAYOFYEAR(p.service_date) BETWEEN 1 AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
          ELSE
              DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
          END)
    AND c.email_service = 1 
ORDER BY
    p.service_date ASC

If you are worried about leap years, you could change the DAYOFYEAR('2018-12-31') to DAYOFYEAR(CONCAT(YEAR(NOW), '-12-31'))

Upvotes: 0

Jeff
Jeff

Reputation: 1017

Worked this out with php

// Get the dates m-d for the next 45 days
for ($i = 0; $i <= 45; $i++) {
    $days = '+' . $i . ' days';
    $date = date('m-d', strtotime($days));
    if ($i != 45) {
        $DateQuery .= "'$date',";
    } else {
        $DateQuery .= "'$date'";
    }
}

and Inserted the dates I needed directly into the query

    SELECT
       p.*,
       c.company 
    FROM
       products p 
       LEFT JOIN
          customers c 
          ON c.id = p.id 
    WHERE
       DATE_FORMAT(p.service_date, '%m-%d') IN 
       (
           $DateQuery
       )
       AND c.email_service = 1 
    ORDER BY
       p.service_date ASC

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

I don't know why your original query was utilizing Date_Format() on a column (service_date), already stored in MySQL Date type, for range comparisons. It is not able to use any indexing (if defined) either.

You simply need to add/subtract Interval to the date: 

SELECT
   p.*,
   c.company 
FROM
   products p 
   LEFT JOIN
      customers c 
      ON c.id = p.id 
WHERE
   p.service_date >= CURDATE() 
   AND p.service_date <= (CURDATE() + INTERVAL 45 DAY) 
   AND c.email_service = 1 
ORDER BY
   p.service_date ASC

Upvotes: 0

Related Questions