MySQL Query for Retirement date calculation based on date of birth considering end of the month and weekday

Develop a query to display the retirement age of an employ based on the date of birth. The following additional points are to be considered.

Retirement age is 60 years.
All employee will retire on last day of the retirement month. Example, if someone completes 60 years on 5th May 2016 then retirement day will be 31st May 2016.
Sunday is a holiday, so if last day of the month is a Sunday then previous day or the last working day will be considered as retirement day.

You will develop this by using a single query and without using any scripting language.

For easy understanding we will develop this query in different steps.
First let us calculate the date of completion of 60 years by adding 60 Years to Date of Birth.
For this we will use DATE_ADD() function.

select DATE_ADD( '1956-07-15', INTERVAL 60 YEAR )

Output is 2016-07-15

We get the retirement day of the month, but now we will calculate the last day of the same month

SELECT LAST_DAY( DATE_ADD( '1956-07-15', INTERVAL 60 YEAR ) )

Output is 2016-07-31

Now we will check the last day is Sunday or not by using DAYOFWEEK() function. This function will return 1 for Sunday , 2 for Monday .....

SELECT DAYOFWEEK( LAST_DAY( DATE_ADD( '1956-07-15', INTERVAL 60 YEAR ) ) )

Output is 1

We will use DATE_SUB() to get the Previous day of the Last Date of the month.

SELECT DATE_SUB( LAST_DAY( DATE_ADD( '1956-07-15', INTERVAL 60 YEAR ) ) ,INTERVAL 1 DAY )

Output is 2016-07-30
We will use IF function to check the Output and see if Last day of the month is a Sunday or not and return the date accordingly.

SELECT IF( DAYOFWEEK( LAST_DAY( DATE_ADD( '1956-07-15', INTERVAL 60 YEAR ) ) )=1 , 
DATE_SUB( LAST_DAY( DATE_ADD( '1956-07-15', INTERVAL 60 YEAR ) ) ,INTERVAL 1 DAY ) , 
LAST_DAY( DATE_ADD( '1956-07-15', INTERVAL 60 YEAR ) ) )

Output is 2016-07-30

Final Query is here by taking another date.

SELECT IF( DAYOFWEEK( LAST_DAY( DATE_ADD( '1956-08-15', INTERVAL 60 YEAR ) ) ) =1, 
          DATE_SUB( LAST_DAY( DATE_ADD( '1956-08-15', INTERVAL 60 YEAR ) ) ,INTERVAL 1 DAY ) , 
          LAST_DAY( DATE_ADD( '1956-08-15', INTERVAL 60 YEAR ) ) );

Output is 2016-08-31

You can see for July month the last working day is considered as 30th ( since 31st July 2016 is Sunday ) but for Aug month it is 31st
SQL txt file for DAYOFWEEK()
SQL Date References MONTH() DAY() LAST_DAY() : Last Day of the month WEEKDAY() : 0 for Monday .. 6 for Sunday

Subscribe to our YouTube Channel here

Retirement day and month from Date of Birth

Subscribe