Monday, June 29, 2009

Find the last day of the month

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)
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
Set @InMonth = 1
Set @InYear = @InYear + 1

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)


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


No comments:

Post a Comment