When you have a database, it may be interesting to be able to retrieve the list of members a few days before an anniversary date or a list of annual events a few days before a date defined in one of the columns. There are several possible reasons for this:
- A site may want to send a promotional offer a few days before the registration anniversary date. Ditto for sending an offer a few days before the member’s birthday.
- A site can retrieve the list of members whose birthday is in a few days. For an online sales site, retrieving members whose birthdays arrive in a few days allows you to send a promotional letter that will reach the customer on the same day of their birthday.
- Retrieve the list of annual events that will soon occur and that recur every year on the same day.
To perform such an SQL query, you have to be cunning because no real function can return a difference of N days compared to a specific date (taking into account only the month and the day).
DATEDIFF () functions
The DATEDIFF () function is used to compare the number of days of difference between 2 dates. Unfortunately, if we compare 2 dates from 2 different years, the difference will be very important. The difference corresponds in a way to the difference between the 2 days + 365 times the number of years of difference.
DAYOFYEAR () function
The trick is to use the DAYOFYEAR () function which returns the number of the day relative to January 1 of each year. For example, February 20 is the 51st day of the year. This function is very useful for comparing the number of days of difference between 2 dates without taking the year into consideration.
To retrieve the list of members who will have their birthday in 10 days, perform the following request:
SELECT * FROM membre WHERE dayofyear(date_anniversaire) - dayofyear(NOW()) = 10 OR dayofyear(date_anniversaire) + 365 - dayofyear(NOW()) = 10
Explanation of this query:
- DAYOFYEAR (birthday_date): returns the day of the member’s birthday
- DAYOFYEAR (NOW ()): returns the current day in the year
- DAYOFYEAR (birthday_date) – DAYOFYEAR (NOW): returns the number of days before the birthday occurs. If the number is negative, it means that the anniversary has already passed or that it was at the beginning of the year.
- DAYOFYEAR (birthday_date) + 365 – DAYOFYEAR (NOW): allows you to take into account if the member’s birthday is between January 1 and January 10.
Warning : there is a small limitation to this tip. In leap years, between January 1 and January 10, the query will return members who have their birthday in 9 days (because a leap year has 366 days).