Starting with SQL Server 2012, the EOMONTH() function allows you to find the last day of any given month. It accepts two arguments; one for the start date, and one optional argument to specify how many months to add to that date.

This article provides examples that demonstrate how EOMONTH() works in SQL Server.

Syntax

First, here’s the syntax:

EOMONTH ( start_date [, month_to_add ] )

Where start_date is the date for which you want to find the last day of the month, and month_to_add is how many months (if any) you’d like to add to the start date.

The EOMONTH() function returns a value in the date data type.

Example 1

Here’s a basic example to demonstrate how it works:

SELECT EOMONTH( '2025-01-01' ) AS Result;

Result:

+------------+
| Result     |
|------------|
| 2025-01-31 |
+------------+

In this case, our start month is January, so the result shows us that the last day of January is 31.

Example 2 – Add a Month

Here’s an example of using a second argument to specify how many months to add. In this case, I add one month to the start date:

SELECT EOMONTH( '2025-01-01', 1 ) AS Result;

Result:

+------------+
| Result     |
|------------|
| 2025-02-28 |
+------------+

And the result shows us the last day of February. This is because we added one month to the start date.

Example 3 – Subtract a Month

You can use a negative number to subtract one or more months from the start date. Like this:

SELECT EOMONTH( '2025-01-01', -1 ) AS Result;

Result:

+------------+
| Result     |
|------------|
| 2024-12-31 |
+------------+

Example 4 – Using the System Date

Here’s an example of getting the end of the month from the current date:

SELECT 
    SYSDATETIME() AS 'Current Date',
    EOMONTH( SYSDATETIME() ) AS 'End of Month';

Result:

+-----------------------------+----------------+
| Current Date                | End of Month   |
|-----------------------------+----------------|
| 2018-06-04 22:53:32.7694823 | 2018-06-30     |
+-----------------------------+----------------+

As mentioned, the EOMONTH() function returns its value in the date data type. That’s why the result in this example shows a discrepancy between how the start date is displayed and how the end of the month is displayed.

In this case, our start date is generated with the SYSDATETIME() function, which returns its value as a datetime2(7) data type. This data type includes the time component as well as the date. The date data type only includes the date component.

You can always format the date using the FORMAT() function, or use various other TSQL functions to extract various parts of the date.

Example 5 – Countdown to the End of the Month

You can also combine the EOMONTH() function with other functions to achieve a desired result.

Here’s an example that returns how many days, hours, minutes, and seconds until the end of the month:

SELECT 
    FORMAT(SYSDATETIME(), 'dd MMMM') AS 'Current Date',
    FORMAT(EOMONTH( SYSDATETIME() ), 'dd MMMM') AS 'EOM',
    DATEDIFF(day, SYSDATETIME(), EOMONTH( SYSDATETIME() )) AS 'Days',
    DATEDIFF(hour, SYSDATETIME(), EOMONTH( SYSDATETIME() )) AS 'Hours',
    DATEDIFF(minute, SYSDATETIME(), EOMONTH( SYSDATETIME() )) AS 'Minutes',
    DATEDIFF(second, SYSDATETIME(), EOMONTH( SYSDATETIME() )) AS 'Seconds';

Result:

+----------------+---------+--------+---------+-----------+-----------+
| Current Date   | EOM     | Days   | Hours   | Minutes   | Seconds   |
|----------------+---------+--------+---------+-----------+-----------|
| 04 June        | 30 June | 26     | 601     | 36055     | 2163252   |
+----------------+---------+--------+---------+-----------+-----------+