CODEWITHSUNDEEP
mysqldate-arithmetic
user7070541
user7070541

Reputation:

Select next 7 days using only day and month

I have a table with birthdays in them, formatted in YYYY-MM-DD. My aim is to return results if the birthday is within the next 7 days.

I need it to use only the month and day, because if it reads the year also, a birthday in 1993 is never going to be within the next 7 days. It also needs take month changes into consideration.

For example, if its the 28th of Feb, and a birthday in the table is on the 1st March, that would be within 7 days, but not within the same month.

SELECT * FROM user WHERE DATE_FORMAT(birthday, '%m-%d') >= DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%m-%d')

This is where i'm at, at the moment, but I know i'm miles off.

Upvotes: 3

Views: 710

Answers (3)

Ullas
Ullas

Reputation: 11556

Take year part from today's date and concatenate it with the month and day part of the dob column value. And cast that string to date. Then use that as a sub-query and check whether thet new column day difference is between 0 and 7.

Query

SELECT t.* -- select except the `new_col` 
FROM  (SELECT *, 
          CAST((Concat(YEAR(NOW()), '-', DATE_FORMAT(dob, '%m-%d'))) AS DATE 
          ) AS 
                `new_col` 
   FROM `user`)t 
WHERE DATEDIFF(t.`new_col`, NOW()) BETWEEN 0 AND 7;

Upvotes: 0

Laurent PELE
Laurent PELE

Reputation: 487

retrieve current year with YEAR(CURDATE(), make a date biy concatenating with month and day coming from birthday and remove 7 days, check if curdate is in the range :

select *
from user 
where 
Curdate() between date_sub(CONCAT(YEAR(CURDATE()),'-',    date_format(birthday, '%m-%d')), interval 7 day) 
and CONCAT(YEAR(CURDATE()),'-', date_format(birthday, '%m-%d')) ;

Upvotes: 0

Matt
Matt

Reputation: 15061

You need to format both of your dates in the WHERE clause.

SELECT * 
FROM user 
WHERE DATE_FORMAT(birthday, '%m-%d') BETWEEN DATE_FORMAT(NOW(), '%m-%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m-%d')

Upvotes: 1

Related Questions