MySQL – Get the First Day of the Month (for a given date)

This is one of those simple problems for which a multitude of answers present themselves. But which is better?

For starters, your "given date" could be the current date, or an arbitrary date in the past or in the future. It can be a string, or a date object, or datetime object.

Which is the best solution? It depends! Some are more "readable", others are faster.

In terms of speed, it seems that there are only slight differences depending on which methodology you use to get the first day of the month. What is more significant is whether you need a "date" or "datetime" result or just varchar. But you usually end up doing something with your result, so date or datetime is usually what you need.

Executive summary: all methods for playing around with dates are extremely fast,  so you should use the method that you find to be most readable. Still, our tests show that solutions that use string manipulation and return VARCHAR are the fastest. If you want DATETIME, then that'll thake about three times as long, while methodologies that result in a DATE take 6-10x as long.

In any case, here are some possible solutions (in no particular order) to this problem, along with benchmarks:

For all of our tests we set @date to an arbitrary DATETIME value:
SET @date := TIMESTAMP('2020-01-11 01:00:00');

You can run the tests yourself if you like:

SELECT BENCHMARK(100000000, LAST_DAY(@date) + INTERVAL 1 DAY - INTERVAL 1 MONTH) AS first_day_of_month;
SELECT BENCHMARK(100000000, DATE(@date) - INTERVAL (DAYOFMONTH(@date) - 1) DAY) AS first_day_of_month;
SELECT BENCHMARK(100000000, DATE(@date) - INTERVAL (DAYOFMONTH(@date)) DAY + INTERVAL 1 DAY) AS first_day_of_month;
SELECT BENCHMARK(100000000, DATE_FORMAT(@date,'%Y-%m-01')) AS first_day_of_month;
SELECT BENCHMARK(100000000, DATE(DATE_FORMAT(@date,'%Y-%m-01'))) AS first_day_of_month;
SELECT BENCHMARK(100000000, TIMESTAMP(DATE_FORMAT(@date,'%Y-%m-01'))) AS first_day_of_month;
SELECT	BENCHMARK(100000000, LAST_DAY(@date - INTERVAL 1 MONTH) + INTERVAL 1 DAY) AS first_day_of_month;
SELECT BENCHMARK(100000000, CONCAT(LEFT(@date, 7), '-01')) AS first_day_of_month;
SELECT BENCHMARK(100000000, DATE(CONCAT(LEFT(@date, 7), '-01'))) AS first_day_of_month;
SELECT BENCHMARK(100000000, CAST(CONCAT(LEFT(@date, 7), '-01') AS DATE)) AS first_day_of_month;
SELECT BENCHMARK(100000000, CAST(CONCAT(LEFT(@date, 7), '-01') AS DATETIME)) AS first_day_of_month;
SELECT BENCHMARK(100000000, TIMESTAMP(CONCAT(LEFT(@date, 7), '-01'))) AS first_day_of_month;
SELECT	BENCHMARK(100000000, MAKEDATE(YEAR(@date), 1) + INTERVAL MONTH(@date) - 1 MONTH) AS first_day_of_month;

#1: LAST DAY + 2x date arithmetic date

"Get first day of next month and backtrack one month"

SELECT DATE_ADD(DATE_ADD(LAST_DAY(@date), INTERVAL 1 DAY), INTERVAL -1 MONTH) AS first_day_of_month;

This is equivalent to using date arithmetic (as proven in this stackoverflow response by "Strawberry"):

SELECT LAST_DAY(@date) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS first_day_of_month;

Since using DATE_ADD is no different than doing date arithmetic - and date arithmetic is more readable in my opinion - in all further examples we will just use date arithmetic even if it could be written using MySQL functions.

#2: DATE + DAYOFMONTH + date arithmetic DATE

"Subtract one less than the current day of the month"

SELECT DATE(@date) - INTERVAL (DAYOFMONTH(@date) - 1) DAY AS first_day_of_month;
#Equivalent to:
SELECT DATE_ADD(DATE(@date), INTERVAL -DAYOFMONTH(@date)+1 DAY) AS first_day_of_month;

Note that it is necessary to initially cast our value to a date, otherwise we will get a VARCHAR value that includes any time component we already have in our value. So, for example this returns "2020-01-01 01:00:00" - which is probably not what we want:

SET @date := TIMESTAMP('2020-01-11 01:00:00');
SELECT DATE_ADD(@date, INTERVAL -DAYOFMONTH(@date)+1 DAY) AS first_day_of_month;

Also note that there is no difference between DAYOFMONTH and DAY, the two are synonyms for each other as stated in the MySQL manual:

DAY() is a synonym for DAYOFMONTH().

#3: DATE + DAYOF MONTH + 2x date arithmetic DATE

This is very similar to #2 except perhaps a bit more readable:

SELECT DATE(@date) - INTERVAL (DAYOFMONTH(@date)) DAY + INTERVAL 1 DAY AS first_day_of_month;

#4: Just DATE_FORMAT it! VARCHAR(10) / DATE / DATETIME

"Cast the date to string (and back to a date is needed) using DATE_FORMAT"

The result here is VARCHAR unless you then cast it to DATE or DATETIME:

4a: SELECT DATE_FORMAT(@date,'%Y-%m-01') AS first_day_of_month;
4b: SELECT DATE(DATE_FORMAT(@date,'%Y-%m-01')) AS first_day_of_month;
4c: SELECT TIMESTAMP(DATE_FORMAT(@date,'%Y-%m-01')) AS first_day_of_month;

#5: LAST_DAY + 2x date arithmetic date

"Get the last day of the previous month and add a day"

SELECT	LAST_DAY(@date - INTERVAL 1 MONTH) + INTERVAL 1 DAY AS first_day_of_month;

#6: CONCAT+LEFT VARCHAR(10) / DATE / DATETIME

"Cast the date to string, take the first 7 characters, and append "-01", and if necessary cast it back to DATE or DATETIME"

6a: SELECT CONCAT(LEFT(@date, 7), '-01') AS first_day_of_month;
6b: SELECT DATE(CONCAT(LEFT(@date, 7), '-01')) AS first_day_of_month;
6c: SELECT CAST(CONCAT(LEFT(@date, 7), '-01') AS DATE) AS first_day_of_month;
6d: SELECT CAST(CONCAT(LEFT(@date, 7), '-01') AS DATETIME) AS first_day_of_month;
6e: SELECT TIMESTAMP(CONCAT(LEFT(@date, 7), '-01')) AS first_day_of_month;

#7: MAKEDATE + 1x date arithmetic date

"Get the first day of the given year and add months to is"

SELECT BENCHMARK(100000000, CAST('2020-01-11 01:00:00' AS DATETIME)) AS first_day_of_month; 

Bonus: From VARCHAR to DATE/DATETIME

Several of the solutions above return a VARCHAR result, which can then be converted to date or datetime depending on our needs. This conversion seems to require the most overhead, and it can be done with DATE(...), CAST(... as ...), or CONVERT(..., ...). Is there a difference? Between CAST and CONVERT NO, there is no difference (as explained in this article, which refers to SQL server but the same applies to MySQL server).

From our tests it looks like it doesn't matter how you convert your values, however, it does take about half as long to convert to DATETIME than to DATE:

You can run the tests yourself to validate our findings:

SELECT BENCHMARK(100000000, CAST('2020-01-11 01:00:00' AS DATETIME)) AS first_day_of_month; 
SELECT BENCHMARK(100000000, CONVERT('2020-01-11 01:00:00', DATETIME)) AS first_day_of_month;
SELECT BENCHMARK(100000000, TIMESTAMP('2020-01-11 01:00:00')) AS first_day_of_month; 
SELECT BENCHMARK(100000000, CAST('2020-01-11 01:00:00' AS DATE)) AS first_day_of_month; 
SELECT BENCHMARK(100000000, CONVERT('2020-01-11 01:00:00', DATE)) AS first_day_of_month;
SELECT BENCHMARK(100000000, DATE('2020-01-11 01:00:00')) AS first_day_of_month; 
SELECT BENCHMARK(100000000, DATE(TIMESTAMP('2020-01-11 01:00:00'))) AS first_day_of_month;

Methodology

To determine the actual return type of the result, we stored it in a temp table and used DESCRIBE to see what the resulting column type was (as suggested by "Brilliand" in a related question on StackOverflow). For example:

DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp AS
SELECT DATE_ADD(DATE_ADD(LAST_DAY(@date), INTERVAL 1 DAY), INTERVAL -1 MONTH) AS first_day_of_month;
DESCRIBE temp;

To benchmark the results, we used...the BENCHMARK function! Continuing our example above:

SELECT BENCHMARK(100000000, LAST_DAY(@date) + INTERVAL 1 DAY - INTERVAL 1 MONTH) AS first_day_of_month;

Another option would have been to create a table of many dates (as described by "IvanD" on stackoverflow) and run the query on that - but this is more messy than the simple BENCHMARK above, especially since our table can only have so many rows because the MySQL date datatype is limited in range, and running the above solutions on only 99 000 rows takes less than a second:

DROP TABLE IF EXISTS t;
CREATE TABLE t (d DATE);

INSERT INTO t 
SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (
	SELECT singles + tens + hundreds + thousands + tenthousands AS number 
	FROM 
		( 
			SELECT 0 singles
			UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
			UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
			UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
		) singles JOIN 
		(
			SELECT 0 tens
			UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
			UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
			UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
		) tens  JOIN 
		(
			SELECT 0 hundreds
			UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
			UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
			UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
		) hundreds  JOIN 
		(
			SELECT 0 thousands
			UNION ALL SELECT  1000 UNION ALL SELECT  2000 UNION ALL SELECT  3000
			UNION ALL SELECT  4000 UNION ALL SELECT  5000 UNION ALL SELECT  6000
			UNION ALL SELECT  7000 UNION ALL SELECT  8000 UNION ALL SELECT  9000
		) thousands  JOIN 
		(
			SELECT 0 tenthousands
			UNION ALL SELECT  10000 UNION ALL SELECT  20000 UNION ALL SELECT  30000
			UNION ALL SELECT  40000 UNION ALL SELECT  50000 UNION ALL SELECT  60000
			UNION ALL SELECT  70000 UNION ALL SELECT  80000 UNION ALL SELECT  90000
		) tenthousands
		# going past tenthousands results in datetime field overflow...
	ORDER BY number DESC
	) c 
;
   
SELECT DATE_ADD(DATE_ADD(LAST_DAY(d), INTERVAL 1 DAY), INTERVAL -1 MONTH) AS first_day_of_month
FROM t;


SELECT COUNT(DISTINCT DATE_ADD(DATE_ADD(LAST_DAY(d), INTERVAL 1 DAY), INTERVAL -1 MONTH)) AS first_day_of_month
FROM t;

Sources and related content:

• "MySQL – Finding First day and Last day of a Month" by Pinal Dave
• "How do I select between the 1st day of the current month and current day in MySQL?" on StackOverflow
• "How to get first day of every corresponding month in mysql?" on StackOverflow