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))
