Code to get the last day of the month in SQL Server

To get the last day of the month you can use this code (result will be the whole date):

DECLARE @date DATETIME
SET @date='2012-06-25'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS LastDayOfTheMonth
GO

To get only the last day:

DECLARE @date DATETIME
SET @date='2012-06-25'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@date)),DATEADD(m,1,@date))) AS LastDayOfTheMonth
GO

And to get the last day of the current month using GETDATE():

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))

This code gets the month of a current date, adds one to it to get the next month. Then subtract one day from the first day of that month, and takes only the day part of the date.

In SQL Server 2012 and later you can use EOMONTH Function to Get First and Last Day of a Month in SQL Server:
EOMONTH Function to Get First and Last Day of a Month in SQL Server

Related blog post:
How to Get First and Last Day of a Month in SQL Server

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">