CODEWITHSUNDEEP
mysqlsql
Joaquín L. Robles
Joaquín L. Robles

Reputation: 6504

How to get students whose 21th birthday is next month in SQL?

I want to obtain all students whose birthday is the next month (I've seen several answers to this) filtering only those who are going to be 21, any idea? My table name is students and the column name is birthdate (DATE), thanks!

Upvotes: 0

Views: 337

Answers (3)

Rahul Tapali
Rahul Tapali

Reputation: 10137

Try this:

 SELECT * FROM students WHERE MONTH(birthdate)=(MONTH(CURDATE()) % 12 + 1)  AND
     (YEAR(CURDATE())-YEAR(birthdate) = 21) ;

DEMO

Upvotes: 0

user1726343
user1726343

Reputation:

Assuming the birthdate column is of a date type, you could use conditions like the following to filter out students who are 20 years old and have a birthday next month.

SELECT * FROM students
WHERE MONTH(birthdate) = (MONTH(NOW()) % 12) + 1
      AND
      TIMESTAMPDIFF(YEAR, birthdate, NOW()) = 20

Upvotes: 2

fthiella
fthiella

Reputation: 49079

If birthdate is of type date, you could use something like this:

SELECT *
FROM   students
WHERE
  birthdate BETWEEN
    LAST_DAY(CURDATE())+INTERVAL 1 DAY - INTERVAL 21 YEAR AND
    LAST_DAY(LAST_DAY(CURDATE())+INTERVAL 1 DAY) - INTERVAL 21 YEAR

Upvotes: 2

Related Questions