SQL Server: Check if Date is End of Month

Here’s a very simple query that checks if the date is End of the Month.

DECLARE @Dt DATETIME 

SET @Dt = '2011-06-30'

IF MONTH(@Dt) != MONTH(DATEADD(DAY,1,@Dt))
BEGIN
PRINT 'End Of Month'
END

As you can see, we are using MONTH(DATEADD(DAY,1,@Dt)) which if run for the June date returns 7 if we are the end of the month. For any other June date, it will return 6. All we are doing is comparing it with MONTH(@Dt) to see if the two values match. If it does not, it is the end of the month.

OUTPUT



SQLServerCurry.com author Madhivanan has suggested an alternate method to check if the given date is the last date of the month. Here it is:

DECLARE @Dt DATETIME
SET @Dt = '2011-06-30'
IF DAY(@Dt+1) = 1
BEGIN
PRINT 'End Of Month'
END
ELSE
BEGIN
PRINT 'Not End Of Month'
END

The code DAY(@Dt+1) adds 1 day to the given date. If the given date is last day of month,
the date becomes first day of next month so the DAY function will return 1. Otherwise it is
not the last day of month.

Update: Brad Schulz made a good point on this alternate method. He says "It will not work with the SQL2008 date datatype. You can not add an integer to a date datatype like you could with datetimes. So instead of DAY(@Dt+1) it is best to do DAY(DATEADD(day,1,@Dt)), which will work in all versions." Thanks Brad!

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook