Get the First Day of a Month in SQL

1. Overview

In this quick tutorial, we’ll learn how to select the first day of a month from a date field using SQL Server, PostgreSQL, and MySQL.

To understand a real-world scenario, we’ll use the Program table in the University database schema that contains a start_date column for different academic programs.

First, let’s start by writing our SQL queries that use the DATEADD and DATEDIFF functions in SQL Server.

2. SQL Server

For SQL Server, we’ll learn three ways to use specific functions. First, we’ll see DATEADD together with DATEDIFF. Then, we’ll look at DATEFROMPARTS and lastly, EOMONTH.

2.1. Using DATEADD and DATEDIFF Functions

To find the first day of the month from the start_date column, we can use the DATEADD and DATEDIFF functions:

SELECT id, name, DATEADD(MONTH, DATEDIFF(MONTH, 0, start_date), 0) AS StartOfMonth
FROM Program;

Above, both functions expect three parameters: the first is a date part, and the second and the third are two dates. First, the DATEDIFF function calculates the difference between two dates, returning the number of date parts between them. In this case, the date parts we’re interested in are months. Then, we use the DATEADD function to add a specified number of date parts to the date.

In short, we’re calculating the difference in months between the base date “1900-01-01” and the start_date. Then, we add this difference to the base date to get the first day of the month for each start_date. Since the base date is the first day of the month, the result is the first day of the corresponding month for each start_date:

| id  | name              | StartOfMonth            |
| --- | ----------------- | ----------------------- |
| 111 | Operating Systems | 2012-01-01 00:00:00.000 |
| 112 | Operating Systems | 2012-01-01 00:00:00.000 |
| 121 | Databases         | 2011-01-01 00:00:00.000 |
| 122 | Databases         | 2011-01-01 00:00:00.000 |

We must remember that this query’s performance depends on the size of the data and the date columns’ indexing.

Now, let’s move next to the DATEFROMPARTS function.

2.2. Using the DATEFROMPARTS Function

Starting from version 2012 of SQL Server, we can use the DATEFROMPARTS function to compose the individual components of the date:

SELECT id, name, DATEFROMPARTS(YEAR(start_date), MONTH(start_date), 1) AS StartOfMonth
FROM Program;

First, we extract the year and month from start_date. Then, we use DATEFROMPARTS to create a new date with the day set to 1.

This method gives us complete control over all the date components, so we can easily construct the first day of the month:

| id  | name              | StartOfMonth |
| --- | ----------------- | ------------ |
| 111 | Operating Systems | 2012-01-01   |
| 112 | Operating Systems | 2012-01-01   |
| 121 | Databases         | 2011-01-01   |
| 122 | Databases         | 2011-01-01   |

One difference from the previous method here is that DATEFROMPARTS returns a date type instead of datetime.

Next, let’s move to the EOMONTH function.

2.3. Using the EOMONTH Function

The EOMONTH function returns the last day of the month for a given date. We’ll use it to find the first day of the month in the start_date column:

SELECT id, name, DATEADD(DAY, 1, EOMONTH(start_date, -1)) AS StartOfMonth
FROM Program;

We subtract one month to get the last day of the previous month. Then, we add one day to get the first day of the month using the DATEADD function. Finally, we get the first day of the month for each start_date:

| id  | name              | StartOfMonth |
| --- | ----------------- | ------------ |
| 111 | Operating Systems | 2012-01-01   |
| 112 | Operating Systems | 2012-01-01   |
| 121 | Databases         | 2011-01-01   |
| 122 | Databases         | 2011-01-01   |

Similar to DATEFROMPARTS, EOMONTH returns the date type.

Next, let’s look at the last method, which uses the DATETRUNC function.

2.4. Using the DATETRUNC Function

Finally, SQL Server 2022 introduces the DATETRUNC function. In short, the DATETRUNC function truncates the date to the start of a specified date part. So, to get the first day of the month from start_date, we can truncate it to the month:

SELECT id, name, DATETRUNC(MONTH, start_date) AS StartOfMonth
FROM Program;

For brevity, we won’t show the result here as it’s the same as the previous example. So, the DATETRUNC function returns the date type.

3. PostgreSQL

In this section, we’ll check two ways to get the first day of the month in PostgreSQL. First, we’ll use the DATE_TRUNC function and then the MAKE_DATE along with the EXTRACT function.

3.1. Using DATE_TRUNC Function

In PostgreSQL, we can use the DATE_TRUNC function to truncate the date to the start of a specified date part. So, we can truncate the start_date column to get the first day of the month:

SELECT id, name, DATE_TRUNC('month', start_date) AS StartOfMonth
FROM Program;

This function truncates the timestamp to the start of the month:

| id  | name              | StartOfMonth                        |
| --- | ----------------- | ----------------------------------- |
| 111 | Operating Systems | 2012-01-01 00:00:00.000000 +00:00   |
| 112 | Operating Systems | 2012-01-01 00:00:00.000000 +00:00   |
| 121 | Databases         | 2011-01-01 00:00:00.000000 +00:00   |
| 122 | Databases         | 2011-01-01 00:00:00.000000 +00:00   |

The DATE_TRUNC returns the type timestamp with timezone.

Let’s move to the following method using the MAKE_DATE together with the EXTRACT function.

3.2. Using MAKE_DATE and EXTRACT Functions

As we did with DATEFROMPARTS in SQL Server, we can create a date by composing its elements. For this purpose, in PostgreSQL, we use the MAKE_DATE function along with the EXTRACT function:

SELECT id, name, MAKE_DATE(EXTRACT(YEAR FROM start_date)::int, EXTRACT(MONTH FROM start_date)::int, 1) AS StartOfMonth
FROM Program;

Similarly, we extract the year and month from start_date and use MAKE_DATE to create a new date with the day set to 1:

| id  | name              | StartOfMonth |
| --- | ----------------- | ------------ |
| 111 | Operating Systems | 2012-01-01   |
| 112 | Operating Systems | 2012-01-01   |
| 121 | Databases         | 2011-01-01   |
| 122 | Databases         | 2011-01-01   |

The MAKE_DATE function returns the date type.

We must keep in mind that MAKE_DATE and DATEFROMPARTS are specific to their respective DBMS; therefore, they aren’t interchangeable.

4. Using MySQL

In this section, we’ll learn two ways to calculate the first day of the month in MySQL. First, we’ll use the DATE_FORMAT function and then the MAKEDATE function.

4.1. Using DATE_FORMAT Function

In MySQL, we can use the DATE_FORMAT function to format the date by fixing the day to 1 and keeping the month and year:

SELECT id, name, DATE_FORMAT(start_date, '%Y-%m-01') AS StartOfMonth
FROM Program;

This formats the date to the first day of the month:

| id  | name              | StartOfMonth |
| --- | ----------------- | ------------ |
| 111 | Operating Systems | 2012-01-01   |
| 112 | Operating Systems | 2012-01-01   |
| 121 | Databases         | 2011-01-01   |
| 122 | Databases         | 2011-01-01   |

The data type of StartOfMonth, in this case, is a varchar(10).

Let’s move on to the following method using the MAKEDATE along with the INTERVAL function.

4.2. Using MAKEDATE With Temporal Intervals

Another way to get the first day of the month in MySQL is to use the MAKEDATE function along with the temporal intervals:

SELECT id, name, MAKEDATE(YEAR(start_date), 1) + INTERVAL (MONTH(start_date) - 1) MONTH AS StartOfMonth
FROM Program;

Above, we use MAKEDATE to create a date corresponding to January 1st of the extracted year. Then, we add the appropriate number of months using INTERVAL.

This method constructs the first day of the month by creating a date from the year and adding the appropriate number of months:

| id  | name              | StartOfMonth |
| --- | ----------------- | ------------ |
| 111 | Operating Systems | 2012-01-01   |
| 112 | Operating Systems | 2012-01-01   |
| 121 | Databases         | 2011-01-01   |
| 122 | Databases         | 2011-01-01   |

Additionally, MAKEDATE returns the date type.

5. Conclusion

In this article, we learned how to calculate the first day of a month from a date field in SQL Server, PostgreSQL, and MySQL.

We used various functions available in each DBMS to achieve this:

• In SQL Server, we used DATEADD, DATEDIFF, DATEFROMPARTS, EOMONTH, and DATETRUNC functions.
• In PostgreSQL, we used DATE_TRUNC and MAKE_DATE with EXTRACT functions.
• In MySQL, we used the DATE_FORMAT function and the MAKEDATE function along with the temporal intervals.