SQL Tips by Namwar Rizvi

May 16, 2007

Calculating last day of the month by TSQL

Filed under: DateTime,tips,TSQL,UDF — namwar @ 7:44 PM

There are several scenarios when you need to calculate the last date of a given month. Problem for the last date is that not every month has the same end date and you can have any thing like 28,29,30,31 depending upon leap year and the required month of the year.
To solve this in a simple query following is a single line code. You can convert it to a UDF also. This query returns the last date of the month for the given date.

—Disable intermediate messages of SQL Server
Set nocount on

—Set date format to day/month/year
Set dateformat ‘dmy’

Declare @m_StartDate datetime

–Set start date as first day of the month
Select @m_StartDate=‘1/12/2007’

—Get the last date of the month
SELECT DateAdd(day, 1, DateAdd( month, DateDiff(month , 0,@m_StartDate)+1 , 0))



  1. Thank you. I uses a similar query before, but I knew that Google would be faster than looking through my notes. ps. You were on top of my Google search “sql calculate last day of the month.”

    I needed to find the last day of the month for the StartDate a year from now, but that part was easy, just add a year to the Start Date variable before you start your calculation:

    SELECT DateAdd(day, -1, DateAdd( month, DateDiff(month , 0, dateadd(yy, 1, @m_StartDate))+1 , 0))

    Comment by Tim — October 9, 2009 @ 7:10 PM | Reply

    • You didn’t have to put another function into the formula to have a year added; just change the plus 1 to plus 13 in the “DateAdd(Month…” formula:

      DateAdd(day, -1, DateAdd( month, DateDiff(month , 0,@m_StartDate)+13 , 0))

      Comment by Larry S. — October 12, 2012 @ 3:20 PM | Reply

RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: