Find the last day of the month

Posted by VIJI Monday, June 29, 2009

About FuncGetLastDayOfMonth SQL User defined function

/* This function accepts the date as a parameter */
/*This function returns the last day of the month of @InDate */


CREATE FUNCTION dbo.FuncGetLastDayOfMonth (@InDate varChar(50)) RETURNS varchar(50)
AS
Begin
Declare @Result as varchar(50)
Declare @InMonth as Int
Declare @InYear As Int
Declare @TempDate As varchar(10)
Declare @TempDate2 As datetime

SET @InMonth = MONTH(@InDate)
SET @InMonth = @InMonth + 1
Set @InYear = YEAR(@InDate)

If @InMonth = 13
BEGIN
Set @InMonth = 1
Set @InYear = @InYear + 1
END

Set @TempDate = CAST(@InMonth as Varchar(2)) + '/01/' + Cast(@InYear as varchar(4))

Set @TempDate2 =DateAdd(d, -1,@TempDate)

Set @Result = CONVERT(varchar(10),@TempDate2,101)

Return (@Result)
End

USAGE

dbo.FuncGetLastDayOfMonth('Your Date')


/* If you pass a pass this function a date, FuncGetLastDayOfMonth(1/12/04)*/
/* Then you will get the last day of January as a result */

/* Example:
dbo.FuncGetLastDayOfMonth('02/01/09') will return 02/28/2009
dbo.FuncGetLastDayOfMonth('05/01/09') will return 05/31/2009



*/

0 Responses to Find the last day of the month

Post a Comment