SQL

Get the Last Date of any Month using SQL SERVER

Sometimes while working with SQL one may need to find "How to find the last day of any month?" or "How to find the first and last day of a month?". Get the first day of any month is relatively simple, but getting the last day of the month can be tricky as the month may have 28,30 or 31 days, and if it's a leap year then we have 29th day in February.

There are two solutions to this problem, depending on version of SQL Server one is using.

1> For SQL Server 2012 and higher 

--EOMONTH ( start_date [, month_to_add ] )  
select EOMONTH (Getdate())  
select EOMONTH (Getdate(),2)  

2> For SQL Server versions lower than SQL Server 2012

The EOMONTH function is not available on on versions less than SQL Server 2012. So we have to create a workaround i.e creating a function that would give the last date of any month which takes datetime as input.

CREATE FUNCTION Get_EndOfMonth
(
	@CurrentDate datetime
)
RETURNS datetime
AS
BEGIN
	declare @Year int = (Select DATEPART(YYYY,@CurrentDate))
	declare @Month int = (Select DATEPART(MM,@CurrentDate))
	declare @Date int = 01
	declare @MonthStart datetime =Cast(@Year as nvarchar(4)) +'-'+ cast(@Month as nvarchar(2)) +'-'+ cast(@Date as nvarchar(2))--+' 23:59:59'
	set @MonthStart= dateadd(MM,1,@MonthStart)
	set @MonthStart= dateadd(DD,-1,@MonthStart)
	-- Return the result of the function
	RETURN @MonthStart

END