How to calculate last date of the month in SQL server?

Calculate the last date of the month is quite simple calculation -

1. Find the total months count till today's date using DATEDIFF(..,..,..) function -

Select DATEDIFF(MM,0,GETDATE())

Output - 1374, If getdate() output is "2014-07-23 19:33:46.850"

2. Increment by 1 into total months count -

Select DATEDIFF(MM,0,GETDATE())+1

Output - 1375, If getdate() output is "2014-07-23 19:33:46.850"

3. Get the first date of next month -

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

Output - '2014-08-01 00:00:00.000', If getdate() output is "2014-07-23 19:33:46.850"

4. Subtract by -1 into the first date of next month, which will return last date of the current month -

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

Output - '2014-07-31 00:00:00.000', If getdate() output is "2014-07-23 19:33:46.850"

In the same manner of calculation we can achieve the -

1. Last date of next month

2. Last date of the previous month

and so on...

I hope this article will help.