SQL First Day of the Month

Date operations are more than a common occurrence in SQL databases. Dates play a crucial role in how we select and operate on the data in SQL databases and are fundamental part of data filtering.

As a result, SQL comes loaded with a truck full of data manipulation functions and methods which allow us to perform the various operations on date and time values.

One such operation is finding the first day of the month, especially when generating the data such as monthly reports, scheduling, or any other time-based operations.

In this guide, we will explore the various methods that you can use to fetch the first day of the month in SQL.

What Is the First Day of the Month?

While it may seem easy and very intuitive to say “first day of the month”, it obviously means the day that is marked as 1^st on the calendar. In SQL, it means converting any given date to its corresponding month’s first day.

Basic Usage:

In most SQL databases, we can take advantage of various date functions to extract the year, month, week, day, etc. from a given date.

We can then reconstruct the data as the first of that month as follows:

This should convert the previous input date into the first date of the month. The resulting output is as follows:

It is good to note that date formatting functions and patterns may vary depending on the database server. For example, in SQL Server, we use the DATEFORMATPARTS function as follows:

This should return the first of the current month.

Conclusion

This covers the most basic methods of fetching the first of the month from a given input date in SQL databases.