SQLServerCentral / Article Discussions / Discuss content posted by ~~Hari.Bagra / Article Discussions by Author / Function to calculate Month End Date / Latest Posts

RE: Function to calculate Month End Date

Lynn Pettis — Sun, 17 May 2009 20:59:43 GMT

[quote][b]vickymae222 (5/17/2009)[/b][hr]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?[/quote]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.

RE: Function to calculate Month End Date

vickymae222 — Sun, 17 May 2009 19:48:59 GMT

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?

RE: Function to calculate Month End Date

Michael Valentine Jones — Sun, 17 May 2009 15:23:38 GMT

[quote][b]vickymae222 (5/17/2009)[/b][hr]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![/quote][code]Select dateadd(mm,datediff(mm,-1,PlanEndDate),-1) as PlanEndOfMonthDatefrom MyTable[/code]

RE: Function to calculate Month End Date

vickymae222 — Sun, 17 May 2009 14:43:37 GMT

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!

RE: Function to calculate Month End Date

Lynn Pettis — Tue, 05 Feb 2008 16:42:52 GMT

Here is another way to do it:[code]CREATE Function GetMonthEnd(@Date DateTime)Returns DateTimeASBegin Return (dateadd(mm, datediff(mm, 0, @Date) + 1, 0) - 1)End[/code]

RE: Function to calculate Month End Date

Jeff Moden — Tue, 05 Feb 2008 16:12:43 GMT

[quote][b]rdijk (2/5/2008)[/b][hr]isn't this a little easier??CREATE Function GetMonthEnd(@Date DateTime)Returns DateTimeASRETURN dateadd(d,(day(@date)) * -1,@date)END[/quote]Heh... only when you actually make it return the correct answer ;)

RE: Function to calculate Month End Date

R. C. van Dijk — Tue, 05 Feb 2008 08:43:26 GMT

isn't this a little easier??CREATE Function GetMonthEnd(@Date DateTime)Returns DateTimeASRETURN dateadd(d,(day(@date)) * -1,@date)END

Function to calculate Month End Date

Hari.Sharma — Sun, 23 Sep 2007 18:24:05 GMT

Comments posted to this topic are about the item [B]Function to calculate Month End Date[/B]