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)
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)
// 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)
// 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:000Step 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.