Results 1 to 7 of 7

Thread: Tighter Function to get last day for a month

  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Post 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

  2. #2
    Join Date
    Dec 2004
    Posts
    502
    Here is an even better method (which I cannot take credit for, unfortunately) ...

    Last Day of Month

  3. #3
    Join Date
    Mar 2005
    Posts
    3

    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

  4. #4
    Join Date
    Sep 2004
    Posts
    6
    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.

  5. #5
    Join Date
    Mar 2005
    Posts
    3

    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

  6. #6
    Join Date
    Apr 2005
    Location
    Kampala
    Posts
    1

    Exclamation 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.

  7. #7
    Join Date
    Sep 2004
    Posts
    6
    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
  •