-
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
-
Here is an even better method (which I cannot take credit for, unfortunately) ...
Last Day of Month
-
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
-
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.
-
time trimming
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
-
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.
-
Good catch I'll have to have another look at the function, makes sense though
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|