CODEWITHSUNDEEP
mysql
user6575251
user6575251

Reputation: 1

Select all users whose birthday is in this week (Monday - Sunday)

I am trying to display all users whose birthday is within this week (Monday to Sunday). After searching everywhere I was able to get this but it's displaying users from last week and this week also.

SELECT * FROM teachers WHERE WEEK(birthday, 0) = WEEK(NOW(), 0)

Upvotes: 0

Views: 248

Answers (3)

Schleis
Schleis

Reputation: 43700

Change the second argument for the WEEK function. I think that you want it to be either 3 or 5. Based on the documentation.

Setting the mode to 0 would get people whose birthdays are on Sunday in the previous week based on what you are saying in your question.

Screenshot of table of mode values

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

Upvotes: 1

Jocelyn
Jocelyn

Reputation: 11393

Use function WEEKOFYEAR:

SELECT * FROM teachers WHERE WEEKOFYEAR(birthday) = WEEKOFYEAR(NOW());

WEEKOFYEAR returns a number in the range 1 to 53.

Upvotes: 0

dlondero
dlondero

Reputation: 2597

Check MySQL's function WEEKOFYEAR. You could use it like this to get users with birthday in current week:

SELECT * FROM teachers WHERE WEEKOFYEAR(birthday) = WEEKOFYEAR(NOW())

Upvotes: 0

Related Questions