Date query in mysql?

Asked

Viewed 43 times

1

I need to consult and introduce the birthday kids of the week at Dashboard system, but today a birthday is not being displayed and would like to know where is the error.

Anniversary column: 1970-02-15 (the date is saved this way in the comic), date and nullable.

Mysql query: SELECT * FROM tbl_usuarios WHERE (WEEK(Aniversario) = WEEK(now()))

We had not noticed anything wrong, until the birthday boy himself questioned us, now we are in doubt if the same should be happening with other users.

mysql

1 answer

2


The scolding is that the function WEEK() returns a number between 0 and 53; according to the year, the day may not be in the same week (e.g.: in a year the day 06/01 is in the week one, in the other he’s in the week two).

One way out would be to add years to the date of birth to check with the current date (using the DATE_ADD()):

SELECT * 
FROM tbl_usuarios 
WHERE (WEEK(DATE_ADD(Aniversario, INTERVAL (YEAR(now()) - YEAR(Aniversario)) YEAR)) = WEEK(now()))
  • 1

    perfect explanation, thanks for contributing.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.