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] );
Copy

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;
Copy

Here is the output:

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

(1 row affected)
Copy

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;
Copy

The output is:

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

(1 row affected)
Copy

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;
Copy

Here is the output:

days
-----------
29
Copy

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

SELECT 
    DAY(EOMONTH(GETDATE()));
Copy

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;
Copy

The output is as follows:

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

(1 row affected)
Copy

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.

Was this tutorial helpful?

YesNo