asked
Trying to output a range of months
Trying to return a range of months between two dates... Alter Function RangeOfMonths ( @StartDate datetime, @EndDate datetime ) Returns @RangeOfMonths Table ( MonthName varChar(15), YearOfMonth int ) Begin Set @EndDate = DATEADD(dd,-(DAY(@EndDate)-1),@EndDate) While @StartDate < @EndDate Begin Insert Into @RangeOfMonths Select DateName(mm, @StartDate) , DateName(yy,@StartDate) Set @StartDate = DATEADD(MM,1,@StartDate) End Return End Go Select dbo.RangeOfMonths( '11-01-2009', '03-30-2010') Should return Nov 2009 up to Feb 2010
@rawilken - you have now posted 7 t-sql questions - please can you start to use the code formatting - it makes for easier reading and will get you answers quicker
either indent code by 4 spaces, or highlight the code in the editor widow and click the 5th button along from the left on the top of the window - it has an icon of '101' over '010'
You have defined a table-valued function, so need to invoke it as a table like: Select MonthName, YearofMonth from dbo.RangeOfMonths( '11-01-2009', '03-30-2010') gives MonthName YearofMonth --------------- ----------- November 2009 December 2009 January 2010 February 2010 (4 row(s) affected)
Using CTE
CREATE FUNCTION RangeOfMonths
(
@StartDate DATETIME ,
@EndDate DATETIME
)
RETURNS @RangeOfMonths TABLE
(
MonthName VARCHAR(15) ,
YearOfMonth INT
)
BEGIN
; WITH CTE
AS ( SELECT 0 ID , @StartDate Date
UNION ALL
SELECT ID + 1 , DATEADD(mm, ID + 1, @StartDate)
FROM CTE
WHERE LEFT(CONVERT(VARCHAR, DATEADD(mm, ID + 1,@StartDate), 112),6)
