LAST_DAY() Last Day of the month in MySQL QueryRetirement 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 by 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.
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
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 .....
Output is 1We will use DATE_SUB() to get the Previous day of the Last Date of the month.
Output is 2016-07-30We 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.
Output is 2016-07-30Final Query is here by taking another date.
Output is 2016-08-31You 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 Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
![]() ▼ More on Date & Time functions in SQL |