Function to calculate Month End Date

  • Comments posted to this topic are about the item Function to calculate Month End Date

  • isn't this a little easier??

    CREATE Function GetMonthEnd(@Date DateTime)

    Returns DateTime

    AS

    RETURN dateadd(d,(day(@date)) * -1,@date)

    END

  • rdijk (2/5/2008)


    isn't this a little easier??

    CREATE Function GetMonthEnd(@Date DateTime)

    Returns DateTime

    AS

    RETURN dateadd(d,(day(@date)) * -1,@date)

    END

    Heh... only when you actually make it return the correct answer 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Here is another way to do it:

    CREATE Function GetMonthEnd(@Date DateTime)

    Returns DateTime

    AS

    Begin

    Return (dateadd(mm, datediff(mm, 0, @Date) + 1, 0) - 1)

    End

  • This is the expression I have been looking for. I have a field called Plan End Date that I need to calculate the end of the month that the Plan End Date is in. How do I write this expression? thank you!

  • vickymae222 (5/17/2009)


    This is the expression I have been looking for. I have a field called Plan End Date that I need to calculate the end of the month that the Plan End Date is in. How do I write this expression? thank you!

    Select

    dateadd(mm,datediff(mm,-1,PlanEndDate),-1) as PlanEndOfMonthDate

    from

    MyTable

  • Thank you for your quick response! What is the My table you are referring to? After I do this expression, then I need to take the PlanEndOfMonthDate and add 15 months for a final recon date. Final Recon Date: DateAdd("m",15,[MonthEnd]). However, it doesn't always give me the correct date. For example. PlanEndOfMonthDate is 2/29/08, the final recon date is coming out as 5/29/09 but I need it to be the end of May, 5/31/09. Any suggestions how to correct my calculation? thanks Michael!

    I tried the following PlanEndOfMonthDate: DateAdd([mm],DateDiff([mm],-1,[PlanEndDate]),-1) and a box comes up for me to enter the month parameter value for mm. How do I get the code to look at the month in the PlanEndDate so parmameter value box is not listed?

  • vickymae222 (5/17/2009)


    Thank you for your quick response! What is the My table you are referring to? After I do this expression, then I need to take the PlanEndOfMonthDate and add 15 months for a final recon date. Final Recon Date: DateAdd("m",15,[MonthEnd]). However, it doesn't always give me the correct date. For example. PlanEndOfMonthDate is 2/29/08, the final recon date is coming out as 5/29/09 but I need it to be the end of May, 5/31/09. Any suggestions how to correct my calculation? thanks Michael!

    I tried the following PlanEndOfMonthDate: DateAdd([mm],DateDiff([mm],-1,[PlanEndDate]),-1) and a box comes up for me to enter the month parameter value for mm. How do I get the code to look at the month in the PlanEndDate so parmameter value box is not listed?

    MyTable is what ever the table you are pulling the data from. You didn't provide any detailed information so a "generalized" table name was provided for you to base your own query on.

    If you would like better answers to your questions I suggest you take the time to read the first article I reference below in my signature block.

  • Select DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))

    Check here for more detail.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply