question

rawilken avatar image
rawilken 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
t-sqlhomework
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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
0 Likes 0 ·
What is code formatting? I have searched the site and cannot find instructions on how to format for asking questions.
0 Likes 0 ·
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'
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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) 
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.