Below are examples of using SQL to return the first day of the month across various DBMSs.

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

SQL Server

As with most things there’s more than one way to do it in SQL Server, but here’s one:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT DATEADD(dd, -( DAY( @date ) -1 ), @date);

Result:

2035-10-01

This involves using some T-SQL functions to perform date shifting in order to get the date back to the start of the month.

See 3 Ways to Get the First Day of the Month in SQL Server for more options.

Oracle

In Oracle, we can do this:

SELECT TRUNC(date '2035-02-15', 'MM')
FROM DUAL;

Result:

01-FEB-35

The TRUNC(date) function returns the given date value with the time portion of the day truncated to the unit provided in the specified format model. In our case, the unit we provided was MM (for the month). This can also be specified as MONTH, MON, or RM.

MySQL

There are several ways to do it in MySQL. One way is to 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

MariaDB

MariaDB pretty much has the same options as MySQL. So one option is to use the code in the above MySQL example.

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

This example can be used in both MariaDB and MySQL.

PostgreSQL

In PostgreSQL, we can use the date_trunc() function to return the results we want:

SELECT date_trunc('month', date '2030-07-14')::date;

Result:

2030-07-01

The date_trunc() function truncates a date/time value to a specified precision.

SQLite

With SQLite, we can achieve our desired result by using the DATE() function:

SELECT DATE('2023-12-10', 'start of month');

Result:

2023-12-01

This function accepts the 'start of month' modifier as the second argument. As the name suggests, this modifier modifies the date to the start of the month.