Similar to a recent posting this function will also return the last day of the month for a date passed, but with less coding.

--Select dbo.udf_GetLastday('2005-02-22',0)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE FUNCTION udf_GetLastday(@dInput datetime,@iAdjVal integer = 0)
RETURNS datetime
AS

--*********************************
--Purpose: Returns Last day of month for date passed,
-- month can be adjusted by @iAdjVal. Zero = current month
--
--
--
-- 02/22/2004 10:06 PM John McLaughlin


BEGIN
DECLARE @dReturn datetime

IF @dInput IS Not NUll
BEGIN

DECLARE @vTmpDate Varchar(20)

SET @vTmpDate = CAST(datepart(yy,@dInput) As Varchar(4))+ '-' + RIGHT('00'+CAST(datepart(mm,@dInput)+1+@iAdjVal As varchar),2) + '-01 23:59:59'
SET @dReturn = Cast(@vTmpDate As datetime)-1
END
ELSE
BEGIN
SET @dReturn = Null
END






RETURN(@dReturn)

END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO