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.
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
PRINT 'End Of Month'
PRINT 'Not End Of Month'
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!