We have 3 options to get first day of the month in MySQL. We can either get first day of current month or first of the month by providing any date of that month. After getting the first date we can add days or perform certain calculations on that. Here is the first way to get the date.
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);We can also use “DATE_ADD()” function.
SET @date:='2028-02-25';
SELECT DATE_ADD(@date, INTERVAL -DAY(@date)+1 DAY);There is another technique. We can use “DATE_ADD()” as well as multiple calls to “LAST_DAY()”.
SET @date:='2028-02-25';
SELECT DATE_ADD(DATE_ADD(LAST_DAY(@date), INTERVAL 1 DAY), INTERVAL -1 MONTH);Here is a real world example from a working database.
CREATE TABLE employees (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
start_date DATE NOT NULL
);
INSERT INTO employees VALUES (0001, 'Joe', '2020-02-15');
INSERT INTO employees VALUES (0002, 'Martha', '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;We are using “CAST()” function to cast DATETIME value to DATE. I hope this article will help you. If you have any question, please write in comments section.
