Tighter Function to get last day for a month
Similar to a recent posting this function will also return the last day of the month for a date passed, but with less coding.
--Select dbo.udf_GetLastday('2005-02-22',0)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION udf_GetLastday(@dInput datetime,@iAdjVal integer = 0)
RETURNS datetime
AS
--*********************************
--Purpose: Returns Last day of month for date passed,
-- month can be adjusted by @iAdjVal. Zero = current month
--
--
--
-- 02/22/2004 10:06 PM John McLaughlin
BEGIN
DECLARE @dReturn datetime
IF @dInput IS Not NUll
BEGIN
DECLARE @vTmpDate Varchar(20)
SET @vTmpDate = CAST(datepart(yy,@dInput) As Varchar(4))+ '-' + RIGHT('00'+CAST(datepart(mm,@dInput)+1+@iAdjVal As varchar),2) + '-01 23:59:59'
SET @dReturn = Cast(@vTmpDate As datetime)-1
END
ELSE
BEGIN
SET @dReturn = Null
END
RETURN(@dReturn)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
First/Last day of month functions
create function getfirstdayofmonth(@dt datetime)
returns datetime as
begin
return @dt-day(@dt)+1
end
create function getlastdayofmonth(@dt datetime)
returns datetime as
begin
return @dt+(32-day((@dt-day(@dt)+1)+31))-day(@dt)
end
GetLastDayFunction needs modification
I found the GetLastDay function very useful for my payroll database application where i needed to use the last days of the months.
I noticed however that it would not work for the month of December - an out of range error is generated. The reason being that on adding 1 the integer for month becomes 13 such that SQL would fail to convert this to datetime.
I modified the function by adding an if statement to check that if the value exceeded 12 i gave it a statement to return which would then be converted to datetime!!
Otherwise it is a very useful script.