Function to calculate Month End Date

Question

Post reply

Function to calculate Month End Date

Hari.Sharma

SSCertifiable

Points: 6904
More actions
September 23, 2007 at 6:24 pm

#177930

Comments posted to this topic are about the item Function to calculate Month End Date
Cheers,
Hari
Tips & Tricks for SQL BI Developers

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

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

R. C. van Dijk SSChasing Mays Points: 603 More actions · Answer 1

isn't this a little easier??

CREATE Function GetMonthEnd(@Date DateTime)

Returns DateTime

AS

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

END

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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

vickymae222 SSC Enthusiast Points: 152 More actions · Answer 4

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 SSC Guru Points: 64818 More actions · Answer 5

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 SSC Enthusiast Points: 152 More actions · Answer 6

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 Points: 442467 More actions · Answer 7

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.

vipulsachan.kiet 1412 SSC Veteran Points: 285 More actions · Answer 8

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

Check here for more detail.