EOMONTH function in SQL Server 2012

sql eomonth example
sql server eomonth function
ms sql server eomonth
sql server 2012 eomonth example
sql server eomonth example
ms sql server 2012 eomonth

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

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

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

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

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

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

Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/eomonth-function-in-sql-server-2012.html