I have attempted to write a query that finds the user's work anniversary,
Table "emp_detail":
| emp_no | join_date | year |
|---|---|---|
| 1 | 2017-07-03 | 5 0 |
| 2 | 2017-07-25 | 4 11 |
| 3 | 2020-07-01 | 2 0 |
| 4 | 2020-07-13 | 2 0 |
| 5 | 2020-07-20 | 1 11 |
| 6 | 2020-08-20 | 1 1 |
| 7 | 2020-02-29 | 2 4 |
Query:
SELECT
emp_no,
join_date,
join_date +
INTERVAL (EXTRACT(YEAR FROM CURDATE()) -
EXTRACT(YEAR FROM join_date)) YEAR
AS "anniversary_date_this_year",
CONCAT(TIMESTAMPDIFF(YEAR, join_date, CURDATE())," ",TIMESTAMPDIFF( MONTH, join_date, CURDATE() ) % 12) as FinalYear
FROM emp_detail
please also see the example
Expected output:
| emp_no | join_date | year |
|---|---|---|
| 1 | 2017-07-03 | 5 year 0 month |
| 2 | 2017-07-25 | 4 year 11 month's |
| 3 | 2020-07-01 | 2 year 0 month |
| 4 | 2020-07-13 | 2 year 0 month |
| 5 | 2020-07-20 | 1 year 11 month's |
| 6 | 2020-08-20 | 1 year 1 month |
| 7 | 2020-02-29 | 2 year 4 month's |
Need help with the "Year" or "Years" alias, 5 Years 0 month, 1 Year 1 month
Pls refer Question for more: How to handle Leap Years in anniversary for a current month in MySQL
Any help would be great!