To get Last Date of the given Month

Posted by T.saravanan under Sql Server category on | Points: 40 | Views : 2909
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)  
// 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 :)

Comments or Responses

Posted by: PandianS on: 11/23/2010 Level:Silver | Status: [Member] [MVP] | Points: 10

Nice Try....

You can also simply use

SELECT @GivenDate = '05/15/2010'
SELECT DATEADD(MM,1,@GivenDate) - DAY(@GivenDate)

Posted by: T.saravanan on: 11/30/2010 Level:Silver | Status: [Member] [MVP] | Points: 10
Thanks PandianS,

Its really nice and easily to understand. Thanks for your response...

Login to post response