# 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

END

• rdijk (2/5/2008)

isn't this a little easier??

CREATE Function GetMonthEnd(@Date DateTime)

Returns DateTime

AS

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.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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