Get data based on day and month from mysql date field-CodePudding

I have a mysql table employees with a column 'Date_of_joining'. I am trying to get the list of employees whose date of joining is closest to the current date

Employees :

EmpID Date_of_joining 
1     2015-08-30
2     2019-01-15 
3     2018-01-11

Expected Result (assuming the current is today ie, 2023-01-09)

EmpID Date_of_joining 
3     2018-01-11
2     2019-01-15 
1     2015-08-30

I am basically trying to find who is approaching their work anniversary. Any clue or help is appreciated

Edit :

The below query works exactly like I want, but i also want to get similar data between two dates, is there way to achieve this using the same query or any tweak to it

SELECT *, (366   DAYOFYEAR(Date_of_joining ) - DAYOFYEAR(NOW())) % 366 as left_days FROM employees ORDER BY left_days

CodePudding user response：

You want to calculate and order by the upcoming anniversary date for each row. This is simply:

case
    when Date_of_joining   interval year(current_date) - year(Date_of_joining) year < current_date then
       Date_of_joining   interval year(current_date)   1 - year(Date_of_joining) year
    else
       Date_of_joining   interval year(current_date) - year(Date_of_joining) year
end

(treating anniversaries of Feb 29 in a non-leap year as Feb 28)

CodePudding user response：

Two functions will help here. DAYOFYEAR(d) returns the day number of the year for the date d. For example, because there are 31 days in Jan, we'd expect DAYOFYEAR('2022-02-01') to return 32.

Next, CURDATE() returns the date for today.

The anniversary of date of joining is always going to be DAYOFYEAR(Date_of_joining). So the number of days between today and date of joining is DAYOFYEAR(CURDATE()) - DAYOFYEAR(Date_of_joining) which you can add to a where clause.

select <choose_a_column>, DAYOFYEAR(CURDATE()) - DAYOFYEAR(Date_of_joining) as delta
from my_table
where delta > 0 # anniversary has passed
and delta < 10 # choose the number yourself