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

MySQL Date Time: Exercise-1 with Solution

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

Sample current date : 2014-09-03
Expected result : 2014-06-01

Code:

-- This SQL query calculates a date that is three months prior to the current date.

SELECT 
date(((PERIOD_ADD -- Calculates a period by adding a specified number of months to a given period.
    (EXTRACT(YEAR_MONTH -- Extracts the year and month from the current date (CURDATE()).
    FROM CURDATE()) -- Specifies the current date.
    ,-3)*100)+1)); -- Subtracts three months from the current date, then multiplies by 100 to convert it to a period, and finally adds 1 to convert it back to a date.

Explanation:

• The CURDATE() function returns the current date.
• The EXTRACT(YEAR_MONTH FROM CURDATE()) extracts the year and month from the current date.
• The PERIOD_ADD function adds a specified number of months to the extracted year and month.
• In this case, -3 is subtracted from the current year and month to get the date three months prior.
• The result is then manipulated to convert it back to a date format, which is done by multiplying by 100 to create a period and then adding 1.
• The date() function is used to convert the result to a date format.

Sample Output:

date(((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-3)*100)+1))
2017-05-01T04:00:00.000Z

MySQL Code Editor:

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

Previous:Date and Time functions
Next:Write a MySQL query to display the last day of the month (in datetime format) three months before the current month.