Getting Last Day of the Month in SQL Server from current date

Note: This article intended for beginners, who are stepping in to learning SQL Server queries. Others might not feel much fancy on this queries.

I created an SQL function for finding the last date of a month using the current date.

Query for creating the function is as follows.

SOURCE CODE

//if exists drop the function GET_END_OF_MONTH and go

IF EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ‘GET_END_OF_MONTH’
AND ROUTINE_SCHEMA = ‘dbo’
AND ROUTINE_TYPE = ‘FUNCTION’
)
BEGIN
DROP FUNCTION dbo.GET_END_OF_MONTH
PRINT ‘Dropped dbo.GET_END_OF_MONTH’
END

go

create function GET_END_OF_MONTH (@date datetime) returns datetime
as
begin

— select check_services.dbo.GET_END_OF_MONTH (getdate())

declare @first_of_month datetime
declare @last_of_month datetime

set @first_of_month = convert(datetime, convert(varchar,datepart(m,@date)) +
‘/01/’ +
convert(varchar,datepart(yyyy,@date) )
)
— first of next month…
set @last_of_month = dateadd(m, 1, @first_of_month)
— …minus one day
set @last_of_month = dateadd(d, -1, @last_of_month)
return @last_of_month

end
go

USAGE

1.) print(dbo.GET_END_OF_MONTH(getDate()))

Output : Jan 31 2010 12:00AM

2.) print(dbo.GET_END_OF_MONTH(getDate()-30))

Output : Dec 31 2009 12:00AM

3.) print(dbo.GET_END_OF_MONTH(DateAdd(Month,16,getDate())))

Output : May 31 2011 12:00AM

In this example i am adding 16 months to current date and trying to get end of the month. So the month was May 18th 2011, so the month end is May 31st 2011 12:00AM

I hope this simple explanation is enough for understading the query.

Any doubts comment on or mail me.

Enjoy Coding!!!