Here are three methods we can use to return the first day of a given month in MySQL.

This could be the first day of the current month, or the first day of the month based on a date that we specify.

Getting the first day of the month allows us to perform further calculations on the resulting date, like adding a certain number of days to the start of the month, etc.

Option 1

We can use the DATE_SUB() function along with the DAYOFMONTH() function to shift the date back to the first of the month:

SET @date:='2028-02-25';
SELECT DATE_SUB(@date, INTERVAL DAYOFMONTH(@date)-1 DAY);

Result:

2028-02-01

Option 2

Another option is to use the DATE_ADD() function:

SET @date:='2028-02-25';
SELECT DATE_ADD(@date, INTERVAL -DAY(@date)+1 DAY);

Result:

2028-02-01

Option 3

This technique uses multiple calls to DATE_ADD(), as well as a call to LAST_DAY():

SET @date:='2028-02-25';
SELECT DATE_ADD(DATE_ADD(LAST_DAY(@date), INTERVAL 1 DAY), INTERVAL -1 MONTH);

Result:

2028-02-01

Database Example

Here’s an example that uses dates from a database:

CREATE TABLE employees (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  start_date DATE NOT NULL
);

INSERT INTO employees VALUES (0001, 'Rohit', '2020-02-15');
INSERT INTO employees VALUES (0002, 'Zohan', '2017-08-09');
INSERT INTO employees VALUES (0003, 'Jen', '2010-12-03');
INSERT INTO employees VALUES (0004, 'Eve', '2011-10-24');
INSERT INTO employees VALUES (0005, 'Ryan', '2021-11-08');

SELECT 
  start_date,
  CAST(DATE_SUB(start_date, INTERVAL DAYOFMONTH(start_date)-1 DAY) AS DATE) AS "First of Month"
FROM employees;

Result:

start_date	First of Month
2020-02-15	2020-02-01
2017-08-09	2017-08-01
2010-12-03	2010-12-01
2011-10-24	2011-10-01
2021-11-08	2021-11-01

In this case I used the CAST() function to cast the datetime value to a date value.