Here are three options for returning the first day of a month in MariaDB.

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

Option 1

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

SET @date:='2030-07-15';
SELECT DATE_SUB(@date, INTERVAL DAYOFMONTH(@date)-1 DAY);

Result:

2030-07-01

Option 2

Another option is to use the DATE_ADD() function:

SET @date:='2030-07-15';
SELECT DATE_ADD(@date, INTERVAL -DAY(@date)+1 DAY);

Result:

2030-07-01

Option 3

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

SET @date:='2030-07-15';
SELECT DATE_ADD(DATE_ADD(LAST_DAY(@date), INTERVAL 1 DAY), INTERVAL -1 MONTH);

Result:

2030-07-01

Database Example

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

SELECT 
    rental_date, 
    CAST(DATE_SUB(rental_date, INTERVAL DAYOFMONTH(rental_date)-1 DAY) AS DATE) AS "First of Month"
FROM rental WHERE customer_id = 459 
LIMIT 10;

Result:

+---------------------+----------------+
| rental_date         | First of Month |
+---------------------+----------------+
| 2005-05-24 22:54:33 | 2005-05-01     |
| 2005-06-17 02:50:51 | 2005-06-01     |
| 2005-06-17 09:38:22 | 2005-06-01     |
| 2005-06-17 16:40:33 | 2005-06-01     |
| 2005-06-20 02:39:21 | 2005-06-01     |
| 2005-06-20 12:35:44 | 2005-06-01     |
| 2005-06-20 12:42:00 | 2005-06-01     |
| 2005-06-21 02:39:44 | 2005-06-01     |
| 2005-07-06 00:22:29 | 2005-07-01     |
| 2005-07-08 02:51:23 | 2005-07-01     |
+---------------------+----------------+

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