# Miscellaneous > SQL Scripts >  Tighter Function to get last day for a month

## jmcl

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

----------


## nosepicker

Here is an even better method (which I cannot take credit for, unfortunately) ...

Last Day of Month

----------


## hafthor

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

----------


## jmcl

I tried the getlastdayofmonth function, passing Getdate() as the parameter, and while it did return the last day the hour was the same as initially returned by the GetDate() function as opposed to 23:59:59. Still it's nice and compact and useful if the time is not an issue.

----------


## hafthor

You can wrap the returned value in a CAST(blahblah-.5 AS INT) to strip off the time. The -.5 is needed because CAST and CONVERT round when converting to int, whereas we just want to do the equiv of FLOOR.

create function getfirstdayofmonth(@dt datetime) 
returns datetime as
begin
return cast(@dt-day(@dt)+.5 as int) -- I was adding 1 before, so +.5 is the same as +1-.5, yeah?
end

create function getlastdayofmonth(@dt datetime)
returns datetime as
begin
return cast(@dt+(32-day((@dt-day(@dt)+1)+31))-day(@dt)-.5 as int)
end

----------


## jomug

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.

----------


## jmcl

Good catch I'll have to have another look at the function, makes sense though

----------

