## Function to calculate Month End Date

 Author Message Hari.Sharma SSCommitted Group: General Forum Members Points: 1760 Visits: 455 Comments posted to this topic are about the item Function to calculate Month End Date Cheers,HariTips & Tricks for SQL BI Developers R. C. van Dijk Old Hand Group: General Forum Members Points: 331 Visits: 87 isn't this a little easier??CREATE Function GetMonthEnd(@Date DateTime)Returns DateTimeASRETURN dateadd(d,(day(@date)) * -1,@date)END Jeff Moden SSC Guru Group: General Forum Members Points: 114058 Visits: 41358 rdijk (2/5/2008)isn't this a little easier??CREATE Function GetMonthEnd(@Date DateTime)Returns DateTimeASRETURN dateadd(d,(day(@date)) * -1,@date)ENDHeh... only when you actually make it return the correct answer --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Lynn Pettis SSC Guru Group: General Forum Members Points: 50751 Visits: 38655 Here is another way to do it:`CREATE Function GetMonthEnd(@Date DateTime)Returns DateTimeASBegin Return (dateadd(mm, datediff(mm, 0, @Date) + 1, 0) - 1)End` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) vickymae222 Grasshopper Group: General Forum Members Points: 14 Visits: 2 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! Michael Valentine Jones SSCertifiable Group: General Forum Members Points: 7440 Visits: 11793 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 PlanEndOfMonthDatefrom MyTable` vickymae222 Grasshopper Group: General Forum Members Points: 14 Visits: 2 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? Lynn Pettis SSC Guru Group: General Forum Members Points: 50751 Visits: 38655 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. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) vipulsachan.kiet 1412 Grasshopper Group: General Forum Members Points: 23 Visits: 22 Select DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))Check here for more detail.