Previous Month End Function

This code is modified from code taken from this site. I�d like to thank the original author, Garth Wells.

Working in finance I often need to calculate the end of the previous month for a specific date. I don�t know why this has always been a challenge but it won�t be anymore. This is a simple SQL function that can be used to return the previous month end date. It follows the usual MS SQL limits for dates and it works for leap years.

-- Function to calculate the end of the previous month from the date input

-- returns date of 1/1/80 if there is a problem

Create FUNCTION fx_LDOPM

( @Date varchar(20) )

RETURNS datetime

AS

BEGIN

--ensure valid date

IF ISDATE(@Date) = 1

BEGIN

--determine first day of month

SET @Date = DATEADD(day,-DAY(@Date)+1,@Date)

--determine last day of the previous month

SET @Date = DATEADD(day,-1,@Date)

END

ELSE

SET @Date = '1/1/80'

RETURN @Date

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Entering this command:

select dbo.fx_LDOPM('2/28/2005')

will return:

2005-01-31 00:00:00.000

Document created 12-9-2003 by John Cutsinger