List of Months & Days Between 2 Dates

Nice work!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Very good

vedaraj

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

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

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

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