MySQL Date and Time Exercises: Display the last day of the month three months before the current month

MySQL Date Time: Exercise-2 with Solution

Write a MySQL query to display the last day of the month (in datetime format) three months before the current month.

Code:

-- This SQL query calculates the last day of the current month.

SELECT 
    (SUBDATE(ADDDATE -- Calculates the date obtained by adding a specified interval (1 month) to the current date.
    (CURDATE(),INTERVAL 1 MONTH), -- Adds 1 month to the current date (CURDATE()).
    INTERVAL DAYOFMONTH(CURDATE()) DAY)) -- Subtracts the number of days from the current date to get the last day of the current month.
    AS LastDayOfTheMonth; -- Alias for the calculated last day of the month.

Explanation:

• The CURDATE() function returns the current date.
• The ADDDATE() function adds a specified interval (1 month) to the current date.
• INTERVAL 1 MONTH specifies adding 1 month to the current date.
• The DAYOFMONTH() function returns the day of the month for the current date.
• The SUBDATE() function subtracts the number of days returned by DAYOFMONTH(CURDATE()) from the date obtained after adding 1 month.
• This effectively gives the last day of the current month.
• The AS LastDayOfTheMonth renames the calculated date column to 'LastDayOfTheMonth' for better readability.

Sample Output:

LastDayOfTheMonth
2017-08-31T04:00:00.000Z

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to display the first day of the month (in datetime format) three months before the current month.
Next:Write a MySQL query to get the distinct Mondays from hire_date in employees tables.