SQL Server 2012 - Effective usage of EOMONTH function‏

Continuing my series on SQL Server 2012, today we will explore a new function in SQL Server 2012 called EOMONTH (End of Month) which can be used to find the last day of the month. In earlier versions, we have to use date functions like dateadd, datediff, etc to find out the last day of the month.

Consider this example for versions prior to SQL Server 2012

declare @date datetime;
set @date = '20120223';
select dateadd(month,datediff(month,-1, @date),-1) as last_day;

The result is 2012-02-29 00:00:00.000

The same can be achieved using EOMONTH function in 2012

declare @date datetime;
set @date = '20120223';
select eomonth ( @date) as last_day;

The result is 2012-02-29

This function can be used in various purposes, like finding last day of next month

declare @date datetime;
set @date = '20120223';
select eomonth ( @date,1) as last_day;

The above results to 2012-03-31. The additional parameter is added as a month and last day is decided.

We can also make use this function to find out first day of month

declare @date datetime;
set @date = '20120223';
select dateadd(day,1,eomonth ( @date,-1)) as first_day;

The EOMONTH finds the last day of previous month and adding one day to its results, fetches the first day of the month.

This way we can effectively make use EOMONTH function for various purposes.