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

image

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

3 comments:

Brad Schulz said...

One word of warning in using Madhivanan's method...

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.


I would discourage mixing integers (or floats for that matter) with any kind of date type... use DATEADD.

--Brad

Suprotim Agarwal said...

Brad: That's a great point! Yes this has been on purpose disabled for the new date and time types in SQL 2008.

An additional comment for the readers - The DATE data type only takes three bytes to store its values as compared to DATETIME which takes eight bytes. SQL 2008 team introduced the Date Type for better precision.


Thanks for your input! I will update the post.

kinh can said...

it is very nice. But i can’t understand DATEDIFF(m,0,getdate()). It returns 1292. Is it month of starting date what date is supported by sqlserver. The reason why you get 1292 is because it gets you the value of the number of months from 1900 (since sql server start date will be from 1900) to the current date.
Assuming month as SEPT.