The following code calculates the last day of the month for any given date:
Description: Calculates the last day of the month for any given date*/CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth]( @inpuDate SMALLDATETIME)RETURNS INT AS BEGIN DECLARE @endDate SMALLDATETIME DECLARE @tempDate SMALLDATETIME -- caluclates end date of the month SET @tempDate = DateAdd(Month, 1, @inpuDate) SET @endDate = DateAdd(DAY, -1, Cast(Cast(Month(@tempDate)As varchar(2)) + '-1-' + Cast(Year(@tempDate)As varchar(4)) As DateTime)) RETURN DATEPART(DAY, @endDate)END