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