SQL Server EOMONTH Function

Summary: in this tutorial, you will learn how to use the SQL Serer EOMONTH() function to get the last day of the month of a specified date.

SQL Server EOMONTH() overview

The EOMONTH() function returns the last day of the month of a specified date, with an optional offset.

The following shows the syntax of the EOMONTH() function:

EOMONTH(start_date [, offset] );
Code language: SQL (Structured Query Language) (sql)

The EOMONTH() function accepts two arguments:

• start_date is a date expression that evaluates to a date. The EOMONTH() function returns the last day of the month for this date.
• offset is an integer that specifies the number of months to add to the start_date.

If the addition of offset and start_date results in an invalid date, the EOMONTH() function will raise an error.

SQL Server EOMONTH() examples

Let’s take some examples of using the EOMONTH() function.

A) Using EOMONTH() function for a date

This example uses the EOMONTH() function to return the last day of the month for the date 2019-02-15:

SELECT 
    EOMONTH('2019-02-15') end_of_month_feb2019;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

end_of_month_feb2019
--------------------
2019-02-28

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

If you pass a date of a leap year to the EOMONTH() function, it still returns the correct result:

SELECT
    EOMONTH('2020-02-09') end_of_month_feb2020;
Code language: SQL (Structured Query Language) (sql)

The output is:

end_of_month_feb2020
--------------------
2020-02-29

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

As clearly shown in the output, the last day of February 2020 is 29th, instead of 28th.

B) Using EOMONTH() function to get the number of days in a specified month

To get the number of days of a specified month, you follow these steps:

• First, use the EOMONTH() function to get the last day of the month.
• Then, pass the last day of the month to the DAY() function.

This example returns the number of days of February 2018:

SELECT 
    DAY(EOMONTH('2020-02-09')) days;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

days
-----------
29
Code language: SQL (Structured Query Language) (sql)

To get the number of days in the current month, you use the following statement:

SELECT 
    DAY(EOMONTH(GETDATE()));
Code language: SQL (Structured Query Language) (sql)

C) Using EOMONTH() with an offset example

The following example uses the EOMONTH() function with an offset of 2 months:

SELECT 
    EOMONTH('2019-02-15', 2) eomonth_next_2_months;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

eomonth_next_2_months
---------------------
2019-04-30

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server EOMONTH() to get the last day of the month of a specified date.