How do you find the last Day of Previous, Current, Or Next Month using MS SQL?

Here are the code for these. At the end we will discuss how it works.

Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

How it Works

Now lets examine one of them to see how it works. We will take last day of current month for our examination.

The code for last day of current month is

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

Syntax for DATEDIFF is

DATEDIFF ( datepart , startdate , enddate )            read more here http://msdn.microsoft.com/en-us/library/ms189794.aspx

This part of the code gives us the number to total months since 1900 upto the current month.

DATEDIFF(m,0,GETDATE())

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

This part of the code below gives us total months since 1900 upto the next month

DATEDIFF(m,0,GETDATE())+1

Syntax for DATEADD is

DATEADD (datepart , number, date )       read more here http://msdn.microsoft.com/en-us/library/ms186819.aspx

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

This part of the code below adds the number of months upto next month to the start date of year 1900. With this we get the date for the start of the next month.

DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)

And finally this part of the code below subtracts 1 second to get the date for the end day of the current month

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))