Function for Determining the First and Last Days of Calendar Months

This Scalar-Valued Function below returns the last day of the month for the date passed into it. For example running:

SELECT [dbo.][fn_GetLastDayOfMonth] ('20090320')

 will return '2009-03-31 00:00:00.000'.

So here's the function:

CREATE FUNCTION [dbo].[fn_GetLastDayOfMonth] (@pInputDate datetime)RETURNS datetime    BEGIN        DECLARE @vOutputDate datetime        SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS decimal(12, 5))) - (DAY(@pInputDate) - 1) AS datetime)        SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))        RETURN @vOutputDate    END

 As a further example, I have a report that needs to be run for last month's numbers.
That is, in May I need to run the report for April. Therefore I create the variables @stardate and @enddate as follows:

DECLARE @startdate datetimeDECLARE @enddate datetimeSET @startdate = (SELECT    DATEADD(dd, 1, [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -2, GETDATE())))                 )SET @enddate = (SELECT  [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -1, GETDATE()))               )

Or for SQL Server 2008+

DECLARE @startdate datetime = (SELECT   DATEADD(dd, 1, [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -2, GETDATE())))                              )DECLARE @enddate datetime = (SELECT [dbo].[fn_GetLastDayOfMonth](DATEADD(mm, -1, GETDATE()))

So, if it's May, 2009 the variables returned are: @startdate = '2009-04-01'@enddate = '2009-04-30'

This function was found at www.sql-server-helper.com.