Please don’t stop the music…while I add the last day of the month to a table

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!

About these ads

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

2 thoughts on “Please don’t stop the music…while I add the last day of the month to a table

  1. toddcarrier says:

    And SQL 2012 provides a simple EOMONTH function to assist with code clarity as well… Check it out,

    SELECT GETDATE(),
    EOMONTH ( GETDATE())

  2. andreaallred says:

    Seriously? Wow, that will be super helpful! Thank you so much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s