Sep 17, 2018

Getting the first day of the month of a date in MySQL

MySQL does not have a function that returns the first day of a date. 

You can get First day by 2 ways:

1. Simply set the day to 1:

SELECT DATE_FORMAT('2017-10-10', '%Y-%m-01')

2. Use the LAST_DAY() function to calculate it.

  • Get the last day of the month of a date.
  • Add 1 day to get the first day of the next month using DATE_ADD() function
  • Subtract 1 month to get the first day of the month of the date.
  1. SELECT 
  2.     DATE_ADD(DATE_ADD(LAST_DAY('2017-10-10'),
  3.             INTERVAL 1 DAY),
  4.         INTERVAL - 1 MONTH) AS first_day;