Find end date for a month

How we can find the first and last day of any month?

This is a very easy code, this function accepts a offset parameter that would help you to find last of any previous months, or coming months.

CREATE FUNCTION getLastDayOfMonth (@dt DATE,@offset INT=0)
RETURNS DATE
AS
BEGIN
 RETURN Dateadd(d,-1,Dateadd(mm,Datediff(m,0,Dateadd(month,1,@dt))+@offset,0));
END
GO

See this function in action here

End_Of_Month_1

In SQL 2012 this much simpler because, SQL Server 2012 provides a build-in function called EOMONTH().

End_Of_Month_2

You can download the full source code here

Reference
http://technet.microsoft.com/en-us/library/hh213020.aspx
http://blogs.msdn.com/b/samlester/archive/2013/09/23/eomonth-equivalent-in-sql-server-2008-r2-and-below.aspx

 

Leave a Reply

Your email address will not be published. Required fields are marked *