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

Posted by Allemahesh under Sql Server on 8/13/2013 | Points: 10 | Views : 2165 | Status : [Member] [MVP] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

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

0	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

0	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

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

Latest Posts