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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan