Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

EOMONTH function in SQL Server 2012

Suggested Videos
Part 122 - IIF function in SQL Server
Part 123 - TRY_PARSE function in SQL Server 2012
Part 124 - TRY_CONVERT function in SQL Server 2012



In this video 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 : 
sql eomonth example

Example : Returns last day of the month of February from a NON-LEAP year
SELECT EOMONTH('2/20/2015') AS LastDay

Output : 
eomonth function in sql server 2012

Example : Returns last day of the month of February from a LEAP year
SELECT EOMONTH('2/20/2016') AS LastDay

Output :
sql server eomonth function

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 : 
ms sql server eomonth

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 : 
sql server 2012 eomonth

Using EOMONTH function with table data. We will use the following Employees table for this example.
sql server 2012 eomonth 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

sql server eomonth example

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

ms sql server 2012 eomonth

1 comment:

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.