List of Months & Days Between 2 Dates

Posted by Ravimakhija88 under Sql Server on 6/15/2012 | Points: 10 | Views : 6032 | Status : [Member] | Replies : 13
Hi All,

I want to find list of months & days between 2 dates.

Eg.From Date - 10/12/2011 To Date - 05/02/2012

Expected Output (that i need):

Months    Days
------- -----
Dec 21
Jan 31
Feb 05


Thanks




Responses

Posted by: CGN007 on: 6/15/2012 [Member] Silver | Points: 25

Up
0
Down
Please try this query

DECLARE @date1 DATETIME, @date2 DATETIME, @lastdate DATETIME, @totalMonths INT,@totalDays INT,@counter INT

SET @date1 = '10/12/2011'
SET @date2 = '05/02/2012'

SET @totalMonths = DATEDIFF(m, @date1, @date2)
IF(@totalMonths < 0)
SELECT 'Second date parameter is prior to the first date parameter'
IF(@totalMonths =0)
SELECT DATENAME(month, @date1) [nameOfMonth],DATEDIFF (d,@date1,@date2) [NumberOfDays]
ELSE
BEGIN
CREATE TABLE #temp_months123
(
[nameOfMonth] VARCHAR(9),
[NumberOfDays] INT
)
SET @counter = 0

SELECT @lastdate= DATEADD( D, -1, DATEADD( mm, DATEDIFF( m, 0, @date1 ) + 1, 0 ) )+1;
WHILE @counter <@totalMonths
BEGIN
SELECT @totalDays=DATEDIFF (d,@date1,@lastdate)
INSERT INTO #temp_months123(nameOfMonth,NumberOfDays) VALUES (DATENAME(month, @date1),@totalDays)
SET @counter = @counter + 1
SET @date1=@lastdate
SET @lastdate = DATEADD(Month, 1, @date1)

END
SELECT @totalDays=DATEDIFF (d,@date1,@date2)+1
INSERT INTO #temp_months123 (nameOfMonth,NumberOfDays) VALUES (DATENAME(month, @date2),@totalDays)
SELECT * FROM #temp_months123
END


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

Posted by: Pandians on: 6/15/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Nice work!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Vedaraj on: 6/15/2012 [Member] Starter | Points: 25

Up
0
Down
Very good

vedaraj

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

Posted by: CGN007 on: 6/16/2012 [Member] Silver | Points: 25

Up
0
Down
@Pandians
Thanks man...

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

Posted by: CGN007 on: 6/16/2012 [Member] Silver | Points: 25

Up
0
Down
Thanks Vedaraj...


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

Posted by: Pandians on: 6/16/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Do you want another simple way ? Without Loop ?

Try this!
Declare @Start Date,@End Date

Select @Start = '2011/12/10', @End='2012/02/05'

;With CTE
As
(
Select @Start [Date]
Union All
Select DateAdd(Day,1,[Date]) [Date] From CTE Where [Date] <=DateAdd(Day,-1,@End)
),Final
As
(
Select Cast(Cast(Year([Date]) as varchar) + '-' + datename(Month,[Date]) + Cast('-01' as varchar) as date) [Seq], Cast(datename(Month,[Date]) as varchar(3)) [Months],Count(1) [Counts] from CTE
Group By datename(Month,[Date]), Cast(Year([Date]) as varchar) + '-' + datename(Month,[Date]) + Cast('-01' as varchar)
)

Select Months, Counts from Final Order by Seq
Go
Result

Months Counts
------ ------
Dec 22
Jan 31
Feb 5
Note:
Use MAXRECURSION when the recursion exceeds 100! (Default is 100 !)

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: CGN007 on: 6/16/2012 [Member] Silver | Points: 25

Up
0
Down
I'll check this also and let u know the feedback...


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

Posted by: CGN007 on: 6/18/2012 [Member] Silver | Points: 25

Up
0
Down
@Pandians
Great...Its Working Fine..

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

Posted by: CGN007 on: 6/18/2012 [Member] Silver | Points: 25

Up
0
Down
@Pandians
Can you tell me which query is fast...?I mean the optimized one.

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

Posted by: CGN007 on: 6/23/2012 [Member] Silver | Points: 25

Up
0
Down
@Ravimakhija88
Mark as answer,if it helps ....


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

Posted by: Kirthiga on: 6/25/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Try this below query it will help you

Declare @StartDay datetime, @EndDay datetime

Set @StartDay='2011-12-10'
Set @EndDay='2012-02-05'

;with cte(Date) as
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select DATENAME(M,Date)Months,count(Date)Days from cte group by DATENAME(M,Date)


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

Posted by: CGN007 on: 7/31/2012 [Member] Silver | Points: 25

Up
0
Down
please mark it as answer....That helps other who search the same...

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

Posted by: Praveenjha on: 4/28/2013 [Member] Starter | Points: 25

Up
0
Down
Hi CGN007 and Pandians,

Your reply helped me a lot. Thank you so much.

But i get to implement a new scenario.
Instead of count of days, i need to count the working days i.e., need to exclude Saturday and Sunday while counting days.

can you guys help me out. I am really stuck at this point. i need to implement this scenario within 2 days.

hope for ur reply asap.

Thanx & Regards,

Praveen

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

Login to post response