How to handle Leap Years in anniversary for a current month in MySQL-CodePudding

I'm attempting to write a query that finds the user's work anniversary for the current month and considers a leap year as well (don't get an idea how to manage within the query)

Table "emp_detail":

emp_no	join_date
1	2002-06-10
2	2022-06-25
3	2020-02-29
4	2002-02-15
5	2011-02-01

So far I have tried the below query:

SELECT no,
       join_date
       CASE WHEN DATEADD(YY,DATEDIFF(yy,join_date,GETDATE()),join_date) < GETDATE() 
            THEN DATEDIFF(yy,join_date,GETDATE())
            ELSE DATEDIFF(yy,join_date,GETDATE()) - 1 
       END AS 'anniversary'
FROM emp_detail
WHERE 'status' = 'active' 
HAVING MONTH(join_date) = 06/07/08 -- ...so on

EDIT: Expected output:

For FEBRUARY month current year 2022

emp_no	join_date	anniversary_date
3	2020-02-29	2022-02-28 (Here, want get 29 Feb 2020 leap year record with non leap year 2022)
4	2002-02-15	2022-02-15
5	2011-02-01	2022-02-01

Looking for a way to display employees with anniversary dates coming up at the start of the current month considering the leap year.

Am I going in the right direction? Any help would be great.

CodePudding user response：

You can make use of extract function in MySQL

select * from emp_detail where extract( month from (select now())) = extract( month from join_date)  and extract( year from (select now())) != extract( year from join_date);

The above query will display all employees whose work anniversary is in the current month.
For the below table:

The above query will display the following rows.

The following query also considers leap year.

If the employee has joined on Feb-29 in a leap year and the current year is a non-leap year, then the query displays Anniversary Date as 'currentYear-Feb-28'

If the employee has joined on Feb-29 in a leap year and the current year is also a leap year, then the query displays Anniversay Date as 'currentYear-Feb-29'

select empId , 
case 
    when ( ( extract(year from (select now()))%4 = 0 and extract(year from (select now()))0 != 0 ) or extract(year from (select now())) % 400 = 0 ) then 
        cast( concat( extract(year from (select now())), '-', extract( month from join_date),'-',  extract( day from join_date) ) as date) 
    when ( ( (extract(year from join_date) % 4 = 0 and extract(year from join_date)0 != 0) or extract( year from join_date)@0 = 0) and extract(month from join_date) =2 
    and extract(day from join_date) = 29 ) then 
            cast( concat( cast( extract(year from (select now())) as nchar), '-02-28') as date)
    else cast( concat( extract(year from (select now())), '-', extract( month from join_date),'-',  extract( day from join_date) ) as date) 
end as AnniversaryDate
from emp_detail
where extract(year from join_date) != extract(year from (select now()));

Emp_detail data

For this data the query will show the following rows

Further if you want to filter the date to current month only, you can make use of extract function.

CodePudding user response：

You can split your problem into two steps:

filtering your "join_date" values using the current month
changing the year to your "join_date"
getting the minimum value between your updated "join_date" and the last day for that date (>> this will handle leap years kinda efficiently wrt other solutions that attempt to check for specific years every time)

WITH cte AS (
    SELECT emp_no,
           join_date,
           STR_TO_DATE(CONCAT_WS('-',
                                 YEAR (CURRENT_DATE()),
                                 MONTH(join_date     ),
                                 DAY  (join_date     )),
                       '%Y-%m-%d') AS join_date_now
    FROM tab 
    WHERE MONTH(join_date) = MONTH(CURRENT_DATE())
      AND YEAR(join_date)  < YEAR(CURRENT_DATE())
)
SELECT emp_no,
       join_date,
       LEAST(join_date_now, LAST_DAY(join_date_now)) AS anniversary_date
FROM cte

Check the demo here

Note: in the demo, since you want to look at February months and we are in July, the WHERE clause will contain an additional -5 while checking the month.

CodePudding user response：

Most (all?) SQL engines already handle year arithmetic involving leap days the way you want: folding the leap day to the final day of February.

So, computing the employee's join_date INTERVAL x YEAR will handle '2020-02-29' correctly. To compute that interval in MySQL/MariaDB for the current year, you may ~~use TIMESTAMPDIFF~~ compute the difference between EXTRACTed years yourself:

SELECT emp_no,
       join_date,
       join_date  
         INTERVAL (EXTRACT(YEAR FROM CURDATE()) -
                   EXTRACT(YEAR FROM join_date)) YEAR
         AS "anniversary_date_this_year",
       ....