Get Last Day of the Month Function

CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME ) RETURNS DATETIME BEGIN DECLARE @vOutputDate DATETIME SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate)) RETURN @vOutputDate END GO Description Here are the steps involved in getting the last day of the month as implemented in the user-defined function above: CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' - This step simply gets the first day of the current month given the input date.  This is achieved by replacing the day part of the input date by 1.  The date is formatted using the "YYYY/MM/DD" date format. DATEADD(M, 1, @FirstDayOfTheMonth) - The result of the previous step, shown here as @FirstDayOfTheMonth, is then incremented by 1 month to get the first day of the following month. DATEADD(DD, -1, @FirstDayOfTheNextMonth) - Lastly, a day is subtracted from the previous step, the first day of the following month shown here as @FirstDayOfTheNextMonth, to get the last day of the month.