List of Months & Days Between 2 Dates

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

Nice work!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Very good

vedaraj

@Pandians
Thanks man...

Thanks Vedaraj...

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

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

@Pandians
Great...Its Working Fine..

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

@Ravimakhija88
Mark as answer,if it helps ....

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)

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

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