To get Last Date of the given Month

Hi All,

Here i am given a Query for how to get last date from given input date.

Declare @sdate varchar(25)
Declare @nMonth int
Set @sdate = '05/15/2010'  (Format:  MM/dd/yyyy)
Set @nMonth=1 

To pass the @sdate and @nMonth value into the following query...

Select DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0 ,@sdate) +@nMonth, 0))
Output:  2010-05-31 00:00:00:000

In this query you can change
@nMonth = 0 (For Previous Month)
@nMonth = 2 (For Next Month)

The main query is splitted into three steps for easily to understand.
I will explain a Query by Functionwise...

Step 1:

  Select DateDiff(mm, 0, @sdate)  
     Output:1324 

// No .of months from minimum date to input date.
In this query "0" is Minimum date in SQL Server.its automatically taken the date "01/01/1900"

Step 2:

  Select DateAdd(mm, 1324, 0)
     Output:2010-05-01 00:00:00:000

// No .of months added into minimum date.
In this query "0" is No.of days Count. Ex:if we given "2" in last argument
Output is: 2010-05-03 00:00:00:000

Step 3:

  Select DateAdd(dd, -1, '2010-05-01 00:00:00:000')
     Output: 2010-04-30 00:00:00:000

// No .of days added into the date.

I hope its useful.
Cheers :)

DECLARE @GivenDate	DATETIME

SELECT @GivenDate = '05/15/2010' 

SELECT DATEADD(MM,1,@GivenDate) - DAY(@GivenDate)