EOMONTH function in SQL Server 2012

we will discuss EOMONTH function in SQL Server 2012

EOMONTH function

• Introduced in SQL Server 2012
• Returns the last day of the month of the specified date

Syntax : EOMONTH ( start_date [, month_to_add ] )

start_date : The date for which to return the last day of the month
month_to_add : Optional. Number of months to add to the start_date. EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

Example : Returns last day of the month November

SELECT EOMONTH('11/20/2015') AS LastDay

Output : 


Example : Returns last day of the month of February from a NON-LEAP year

SELECT EOMONTH('2/20/2015') AS LastDay

Output : 


Example : Returns last day of the month of February from a LEAP year

SELECT EOMONTH('2/20/2016') AS LastDay

Output :


month_to_add optional parameter can be used to add or subtract a specified number of months from the start_date, and then return the last day of the month from the resulting date.

The following example adds 2 months to the start_date and returns the last day of the month from the resulting date

SELECT EOMONTH('3/20/2016', 2) AS LastDay

Output : 


The following example subtracts 1 month from the start_date and returns the last day of the month from the resulting date

SELECT EOMONTH('3/20/2016', -1) AS LastDay

Output : 


Using EOMONTH function with table data. We will use the following Employees table for this example.


SQL Script to create Employees table

Create table Employees

(

    Id int primary key identity,

    Name nvarchar(10),

    DateOfBirth date

)

Go

Insert into Employees values ('Mark', '01/11/1980')

Insert into Employees values ('John', '12/12/1981')

Insert into Employees values ('Amy', '11/21/1979')

Insert into Employees values ('Ben', '05/14/1978')

Insert into Employees values ('Sara', '03/17/1970')

Insert into Employees values ('David', '04/05/1978')

Go

The following example returns the last day of the month from the DateOfBirth of every employee.

SELECT Name, DateOfBirth, EOMONTH(DateOfBirth) AS LastDay

FROM Employees



If you want just the last day instead of the full date, you can use DATEPART function

SELECT Name, DateOfBirth, DATEPART(DD,EOMONTH(DateOfBirth)) AS LastDay

FROM Employees