w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.