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

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

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?