Greetings!

I have this table that Finance uses to tell what the last day of any given month is in their reports. Through people coming and going, the keeper of this table didn’t keep it and the updates to it stopped.  Eventually, reports that used it stop moving forward as the dates didn’t exist.  I decided to create a job to update this table so it wouldn’t matter if someone forgot to update it.  It automatically will update each month (thank you SQLAgent).  But I was still left with the small problem of determining the last day of the month.  I found that the table had the last day of the month before it, so I am using that table to get the my next month end date.  The current table has two columns, the MonthEndDate and the Date that entry was created. I turned this select into an insert and ta-da we have the last date of the next month.

I add a day to the month end day so I get the first day of the next month.  Then I add a month to that and subtract to the day before the first of that month.

For Example:

MAX(MonthEndDate) = ‘5/30/2014’

Add a day and you get ‘6/1/2014’

Add a month and you get ‘7/1/2014’

Subtract a day and you get ‘6/30/2014’

SELECT DATEADD(day,-1,(DATEADD(m,1,(DATEADD(day, 1,MAX(MonthEndDate)))))),GETDATE()

FROM [dbo].[MonthEndDates]

*Update* Todd commented and showed me that you can just do this in SQL 2012:

SELECT EOMONTH(GETDATE())

That is super awesome!  Thank you for sharing Todd!

Enjoy!