DECLARE @date1 DATETIME, @date2 DATETIME, @lastdate DATETIME, @totalMonths INT,@totalDays INT,@counter INT
SET @date1 = '05/02/2012'
SET @date2 = '05/12/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