Question 21368 in Mysql Question posted in
The official MySQL documentation can be found here.

Get data based on day and month from mysql date field

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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)

    Login or Signup to reply.
  3. 0

    I know the query in my answer is complex but I’m posting it anyway if it helps.
    It allows not only to have the anniversary date but also the number of days before the anniversary.

    On the other hand I had to duplicate the code for obtaining the anniversary date to obtain the number of days, I don’t know if there is a better way to do it.

    select *, 
if(date_format(Date_of_joining,'%m%d')<date_format(now(),'%m%d'),concat((year(current_date)+1),date_format(Date_of_joining,'-%m-%d')),concat((year(current_date)),date_format(Date_of_joining,'-%m-%d'))) as 'anniversary',
DATEDIFF(if(date_format(Date_of_joining,'%m%d')<date_format(now(),'%m%d'),concat((year(current_date)+1),date_format(Date_of_joining,'-%m-%d')),concat((year(current_date)),date_format(Date_of_joining,'-%m-%d'))),NOW()) as 'nbDays' 
from employees 
order by anniversary
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search