How to get the last month last day in SQL? [Resolved]

Posted by Allemahesh under Sql Server on 8/13/2013 | Points: 10 | Views : 1130 | Status : [Member] [MVP] | Replies : 7
How to get the last month last day in SQL?

Example:-
1. Today is 13-08-2013. So lat month last day is 31.
2. If I select data is 01-05-2013 then last month last day is 30.

Can any one help me.




Responses

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
SET DATEFORMAT DMY -- This is for setting Date format as DD-MM-YYYY
GO
DECLARE @date DATE ='13-08-2013'   -- Declaration of date input variable

SELECT DATEADD( DD, -DAY(@date), @date) -- This is the logic to get last month last day


-- Another logic to get last month's last day with time portion
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0, @date ), 0))


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/13/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Chandu,
You are grate.
Happy Coding.

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Thank you so much...
To be frank I'm not that much great.....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ssj_Kumar on: 8/13/2013 [Member] Starter | Points: 25

Up
0
Down
DECLARE @dtDate DATE
SET @dtDate = '08/13/2013'
SELECT convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)))

Regards,
Jayakumar Selvakani

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Jayakumar,
DECLARE @dtDate DATE
SET @dtDate = '08/13/2013'
SELECT convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)))

Your query return Last Day of current Month...
For Last Day of Last Month you should remove +1 after DATEDIFF() function
SELECT convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)[b]+1[/b],0)))   


Final Query should be
SELECT convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)))   


However your query is too complicated... You have used 4 functions in your query...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 8/13/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Well Finally I got simple way to find the solutions:-

select getdate() - Day(getdate())

OR

declare @date as date
set @date = '08/13/2013'
select cast(@date as datetime) - Day(@date)

Happy Coding.

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/13/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Mahesh,
Did you get the difference between your query & mine... ?
just for clarification I'm posting these differences.....
--1) Here first part must be DATETIME type... SQL developers will prefer to use DATEADD() function for this kind of queries....
-- 2) time portion will differ...
select getdate() - Day(getdate()) -- 2013-07-31 [b]17:52:22.570[/b]


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response