EOMONTH - Last day of month in SQL Server

In SQL Server 2012, Microsoft has introduced a new in-built function EOMONTH to find out the last date of the month.

In prior version, we have to write our own logical script to find out the same like below

DECLARE @dt DATE
SET @dt = GETDATE()
SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)) AS DATE)

EOMONTH returns the last day of the month for the specified date

Syntax

EOMONTH ( start_date [, month_to_add ] )

Input Parameters

• start_date: date for which to return the last day of the month
• month_to_add: Optional integer specifying the number of months to add to start_date. If this parameter is specified, then specified number of months are added to start_date, and then returns the last day of the month for the resulting date.

NOTE: If this addition results in overflow of valid date, then an error is raised.

Let's use EOMONTH function with some examples

Example 1: With first parameter (start_date) only

DECLARE @date DATE = '01/01/2012'
SELECT EOMONTH ( @date ) AS Result

OUTPUT

Example 2: With both parameters (start_date and month_to_add)

DECLARE @date DATE = '01/01/2012'
SELECT EOMONTH (@date) AS 'This Month'
       , EOMONTH (@date, 1) AS 'Next Month'
       , EOMONTH (@date, -1) AS 'Last Month'

OUTPUT