Get list of months, monthname and Year

  • Hi,

    I want to create a temp table and insert all the list of

    monthorder , Monthnames , year

    1 january 2011

    2 february 2011

    .

    .

    .

    12 December 2011

    Thanks,

    Komal

  • I got query to get month order and year but not getting month names.

    SELECT [year], [month]

    FROM (

    SELECT 2005 AS [year] UNION ALL

    SELECT 2006 UNION ALL

    SELECT 2007 UNION ALL

    SELECT 2008 UNION ALL

    SELECT 2009 UNION ALL

    SELECT 2010 UNION All

    SELECT 2011 UNION All

    SELECT 2012 UNION ALL

    SELECT 2013

    ) years

    CROSS JOIN (

    SELECT 1 AS [month] UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

    ) monthsorder

    ORDER BY years.[year], monthsorder.[month]

    can anybody help getting monthnames in same query??? so that i can create table

  • SELECT [year], [month], datename(month,dateadd(mm,[month] -1,DATEADD(yy, DATEDIFF(yy,0,[year] - 1900), 0)))

    FROM (

    SELECT 2005 AS [year] UNION ALL

    SELECT 2006 UNION ALL

    SELECT 2007 UNION ALL

    SELECT 2008 UNION ALL

    SELECT 2009 UNION ALL

    SELECT 2010 UNION All

    SELECT 2011 UNION All

    SELECT 2012 UNION ALL

    SELECT 2013

    ) years

    CROSS JOIN (

    SELECT 1 AS [month] UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

    ) monthsorder

    ORDER BY years.[year], monthsorder.[month]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • In just a couple of years, this table is only going to contain past dates.

    If you're going to create a calendar table, it should contain more years than this, I would have thought - or be dynamically created as and when required spanning enough years before and after the current date.

    Also, Lowell's suggestion is needlessly complicated - the months have the same name every year, so there is no need to provide a variable year in the MonthName calculation:

    selectt.Number as [year]

    ,m.[month]

    ,DateName(month,DateAdd(month,(m.[month]-1),0)) as MonthName

    fromtally t

    cross join(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) m([month])

    wheret.Number between DatePart(year,GetDate()) - 100 and DatePart(year,GetDate()) + 200

    Of course, this assumes that you have a Tally table handy, but if you haven't, then you should! 😉

  • If you dont have a tally table, you can utilize this code :

    DECLARE @StartDATE DATETIME

    SET @StartDATE = '01-01-1985'

    ;WITH Tens (N) AS

    (

    SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

    ),

    Thousands (N) AS

    (

    SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

    ),

    Tally AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

    ),

    Month_Numbers ( MonthNum , Month_Name) AS

    (

    SELECT 1 , 'January'

    UNION ALL SELECT 2 , 'February'

    UNION ALL SELECT 3 , 'March'

    UNION ALL SELECT 4 , 'April'

    UNION ALL SELECT 5 , 'May'

    UNION ALL SELECT 6 , 'June'

    UNION ALL SELECT 7 , 'July'

    UNION ALL SELECT 8 , 'August'

    UNION ALL SELECT 9 , 'September'

    UNION ALL SELECT 10 , 'October'

    UNION ALL SELECT 11 , 'November'

    UNION ALL SELECT 12 , 'December'

    )

    SELECT Nums.MonthNum , DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [YEAR], DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [MONTH]

    FROM Tally T

    JOIN Month_Numbers Nums

    ON DATENAME(MM,DATEADD(M,N-1,@StartDATE)) = Nums.Month_Name

    WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= YEAR(GETDATE())

    The @StartDate will specify from what year your output should start.

  • A more cleaner version:

    DECLARE @StartDATE DATETIME

    SET @StartDATE = '01-01-1985'

    ;WITH Tens (N) AS

    (

    SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

    ),

    Thousands (N) AS

    (

    SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

    ),

    Tally AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

    )

    SELECT MonthNum =

    CASE T.N % 12

    WHEN 0 THEN 12

    ELSE T.N % 12

    END

    , DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [YEAR]

    , DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [MONTH]

    FROM Tally T

    WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= YEAR(GETDATE())

  • Thank you for all responses...I got it!!!

  • komal145 (8/31/2011)


    Thank you for all responses...I got it!!!

    Cool! Please post the code you ended up using so that we all may learn. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • WITH DateYear AS

    (

    SELECT 0 AS num

    UNION ALL

    SELECT num + 1 FROM DateYear

    WHERE num < 11

    )

    SELECT CONVERT(DATE,DATEADD(MONTH,num,'2011')) AS Date from DateYear

  • Very cool code @ColdCoffee.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (9/3/2011)


    Very cool code @ColdCoffee.

    Thank you Welsh!

  • For even cleaner code, your method needs a 0-based tally table. This is easily accomplished by adding -1 to row_number(). Your code then looks like this:

    DECLARE @StartDATE DATETIME;

    SET @StartDATE = {d '1985-01-01'};

    ;WITH Tens (N) AS

    (

    SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

    ),

    Thousands (N) AS

    (

    SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

    ),

    Tally AS

    (

    SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

    )

    SELECT MonthNum = T.N

    , DATEPART(YY,DATEADD(M,T.N,@StartDATE)) [YEAR]

    , DATENAME(MM,DATEADD(M,T.N,@StartDATE)) [MONTH]

    FROM Tally T

    WHERE DATEPART(YY,DATEADD(M,T.N,@StartDATE)) <= YEAR(GETDATE());



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • R.P.Rozema (9/4/2011)


    This is easily accomplished by adding -1 to row_number().

    Thanks Rozema for "bettering" it out. If zero based tally table will make it cleaner, we can do something like

    Tally AS

    (

    SELECT 0

    UNION ALL

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

    )

    This way, we can stop the Compute Scalar operator and could the engine from calculating for all the rows. Right, R.P.R ?

  • And coming to my code, a 0-based will throw the CASE statement out of sync. That's why i din't use it 🙂

  • Added more details in code and optimized it.

    DECLARE @StartDATE DATETIME

    ,@EndDate DATETIME

    ,@YearofEndDate INT

    SELECT @StartDATE = '01-01-1985' , @EndDate = GETDATE()

    SET @YearofEndDate = YEAR(@EndDate)

    ;WITH Tens (N) AS

    (

    SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0

    ),

    Thousands (N) AS

    (

    SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3

    ),

    Numbers AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands

    )

    SELECT MonthNum =

    CASE T.N % 12

    WHEN 0 THEN 12

    ELSE T.N % 12

    END

    , DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [Year]

    , DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [Month]

    , DATEADD(M,N-1,@StartDATE) [StartDateOfMonth]

    , DATEADD (DAY , -1, DATEADD(MONTH , 1 , DATEADD(M,N-1,@StartDATE)) ) EndDateOfMonth

    FROM Numbers T

    WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= @YearofEndDate

    ORDER BY [Year] , MonthNum

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply