How to use SQL Server EOMONTH Function

Knowing how to manipulate data values is necessary as a database developer, so here I will explain the SQL Server EOMONTH function.

This function can find the last day of any given date; as you know, in businesses, monthly reviews and reports are conducted, like financial statements and inventory audits; here, you can use the EOMONTH() function to determine the reporting periods.

So, in this SQL Server tutorial, I will start by describing the definition of the EOMONTH() function with syntax, and then, with several examples, you will learn how it works.

Finally, you will learn how to use the date function with SQL Server EOMONTH function and then how to use the EOMONTH() function on the table.

SQL Server EOMONTH Function

The SQL Server EOMONTH function returns the last day of the month for the given date. So if you pass the date ‘2024-01-30’, then this function returns the last day of the month of that date.

The syntax of the EOMONTH() function is given below.

EOMONTH(start_date, month_to_add])

Where,

• EOMONTH(): This function returns the last of the month of the specified date.
• start_date: This is the date based on this date; the last day of the month is computed.
• month_to_add: It is an option parameter of type integer. It is used to specify the number of months you want to add to the start_date.

Now, let’s take an example and see how the SQL Server EOMONTH function returns the last day of the date.

You have the date ‘2024-02-01’, and to know the last of the month of that date, use the EOMONTH() function as shown in the query below.

SELECT EOMONTH('2024-02-01') AS LastMonthDay;

When you execute the query above, it returns the date containing the last day of the month of the specified date in the EOMONT(); as you can see, the returned date is ‘2024-02-29’, and the last day is 29 of Feb (02) month.

You can even pass any date to the SQL Server EOMONTH function, and this function will return the last day of the month of that date.

Next, let’s see how to add the month-to-date and then compute the last day of the month of the specified date.

SQL Server EOMONTH Function by Adding Month-to-Date

Here, you will learn how to use the second parameter of the EOMONTH() function, which specifies the number of months that you want to add to start_date.

For example, you have ‘2024-03-01’, add four months to this date, and then compute the last day of the month of the added date. For example, look at the query below.

SELECT EOMONTH('2024-03-01', 4) AS LastMonthDay;

As you can see, the return date is 2024-07-31, and the last day of this date is 31; but here we haven’t the exact date; we passed the date ‘2024-03-01’ and added 4 months to date, so the final date becomes 2024-07-1.

The final date ‘2024-07-01’ is passed to the EOMONTH() function. Thus, it returns the last date as 31.

SQL Server EOMONTH Function by Subtracting Month-to-Date

You can also subtract the month from the specified date and then compute the last day of the month of the date.

For example, again, you have a date ‘2024-06-23’ and subtract the 2 months from the date, as shown below.

SELECT EOMONTH('2024-06-23', -2) AS LastMonthDay;

The above query output returns the date as 2024-04-30, and the last date is 30. we haven’t specified the date ‘2024-04-30’ in the EOMONTH() function; we specified ‘2030-06-23’ and subtracted -2 months from this date.

After subtracting the final date, ‘2030-06-23’ is passed to the EMONTH() function, so it returns last as 30 of the Apr (04) month of the date.

Using Date Function with SQL Server EOMONTH function

You can pass the date function to the EOMONTH() function to compute the last day of the month of the date returned by the function.

For example, to get the current date, pass the GETDATE() function to the EOMONTH() function for computing the last day in the current month of the date. For that, use the query below.

SELECT EOMONTH(GETDATE()) AS LastMonthDay;

The EOMONTH() function returns the date as 2024-01-31, Here we have passed the GETDATE() function, which returns the current date, and this current date is passed to the EOMONTH() function, and the function returns the last day of the current date.

As a result, the last day is the 31 of the current date.

Calculating Number of Days using SQL Server EOMONTH function

The number of days in a month can be computed using the DAY() function with the EMONTH() function.

For example, pass the date ‘2024-08-01’ to the EOMONTH() function and then wrap the whole function with the DAY() function, as shown below.

SELECT DAY(EOMONTH('2024-08-01')) AS NumberOfDays;

The number of days is 31; as you can see from the output, Here we have passed the date ‘2024-08-01’ to the EOMONTH() function; this function returns the date ‘2024-08-31’ containing the last day (31) of the month Aug (08).

Then, this date ‘2024-08-31’ containing the last day is passed to the DAY() function, which extracts the day part of the given date; as a result, it returns 31 as the number of days in the specified date.

SQL Server EOMONTH function on Table

I will show you how to use the SQL Server EOMONTH() function on the table; for example, you have a Projects table, as shown below.

Suppose you are a project manager using the data from the above table. You need to track milestones expected by the end of a particular month. The EOMONTH() function can be used, as shown in the query below.

SELECT ProjectID, 
	EOMONTH(StartDate, ProjectDurationMonths) AS 'Expected Completion'
FROM Projects;

From the output of the above query, the result set contains the expected completion date of the projects based on the StartDate and ProjectDurationMonths.

Here, in the query part, EOMONTH(StartDate, ProjectDurationMonths), the project’s start date is passed and then to each date, corresponding project duration months are added, and the function returns the expected completion date of each project.

For example, the project with an ID equal to 2 has an expected completion date of 2024-04-30.

Conclusion

In this SQL Server tutorial, you have learned how to use the SQL Server EOMONTH function to find the last day of the month of the specified date. I also learned how to add and subtract the months to date and then find the last day of the month of that date.

Additionally, you have learned how to use the date function, such as GETDATE() with the EOMONTH() function; after that, you learned how to use the EOMONTH() function table and compute the expected completion date of the projects.

You may like to read:

• MONTH Function in SQL Server
• How to use SQL Server DATE_BUCKET Function
• ISDATE Function in SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.