SQL EOMONTH Function

The SQL Server EOMONTH is one of the Date Functions used to display the last month of a given date. This EOMONTH function allows you to add a second argument (optional) to move forward and backward.

The basic syntax of SQL Server EOMONTH function is as shown below:

EOMONTH (Date, Months to add)

Date: Please specify the valid date. It can be column data, expression, or any variable.

Months to add: This is an optional argument. Please specify the number of months you want to move:

If you specify the positive integer as the second argument (Number), the function will move forward to a specified number of months. And display the last date of that month.
If you specify the Negative integer as the second argument (Number), the EOMONTH function will move backward to a specified number of months and display the last date of that month.

SQL EOMONTH Example

In this SQL example, we are going to declare a variable of datetime2 data type. Next, we are going to find the last day (in a month) of that variable date.

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'
     
SELECT @Date AS Today, 
       EOMONTH(@Date) AS [Last Day of this Month]

We declared a variable called @Date and assigned the date and time to that variable.

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'

EOMONTH function present in the following statement will find the last day of the 8th month (or August)

EOMONTH(@Date) AS [Last Day of this Month]

SQL Server EOMONTH with Positive and Negative integer

In this EOMONTH example, we are going to use the positive and negative integers as a second argument to show you the last days of a parallel period.

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'
     
SELECT @Date AS Today
     , EOMONTH (@Date, 2) AS [Last Day] 
     , EOMONTH (@Date, -1) AS [Previous Month]

We used two arguments in the EOMONTH function, and the second argument is a positive integer. So, the following statement will move forward to 2 months. It means (8 + 2 = 10) EOMONTH will find the last day of the 10th month (or October).

EOMONTH(@Date, 2) AS [Last Day]

We used two arguments in the EOMONTH function, and this time we used the Negative integer as a second argument. So, the following statement will move backward to 1 month. It means the (8 – 1 = 7) function will find the last day of the 7th month (or July).

EOMONTH(@Date, -1) AS [Previous Month]

EOMONTH Example 2

In this Date function example, we are going to use the Custom query and find out the last day of the current Month, Previous period, and Next period using the SQL Server EOMONTH function.

SELECT [FirstName] + ' '+ [LastName] AS [Full Name]
      ,[Occupation]
      ,[YearlyIncome]
      ,[HireDate]
      ,EOMONTH([HireDate]) AS [This Month]
      ,EOMONTH([HireDate], 2) AS [Next Period]
      ,EOMONTH([HireDate], -2) AS [Previous Period]
 FROM [Employee]