Post reply

Simple query for the Months of a year

  • June 16, 2010 at 12:30 pm

    #236595

    Hi everyone! I build a lot of reports based on months and years. I would love to be able to have a query stored where I can quickly call on to obtain a list of months for my Parameters.

    I can't figure this out!! Does anyone have a query they call on to get a list of the months of the year?

  • June 16, 2010 at 12:36 pm

    #1181228

    To clarify;

    a list that will look like this:

    January 1

    February 2

    ...

  • June 16, 2010 at 1:07 pm

    #1181244

    iklektic (6/16/2010)

    Hi everyone! I build a lot of reports based on months and years. I would love to be able to have a query stored where I can quickly call on to obtain a list of months for my Parameters.

    I can't figure this out!! Does anyone have a query they call on to get a list of the months of the year?

    I'm not really sure what you're asking for here. Do you have an int column that contains 1 to 12 and you want to get the month name? The opposite? Something else? If you want something that'll just give you

    January 1

    February 2

    March 3

    ...

    November 11

    December 12

    It is only 12 rows, make a table and reference it.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • June 16, 2010 at 1:15 pm

    #1181250

    The following script will provide you with a list months.

    declare @dtlist table

    (dt datetime)

    declare @x int

    set @x = 0

    while @x < 12

    begin

    insert into @dtlist select dateadd(mm,@x,'01/01/10')

    set @x=@x+1

    end

    select datename(month,dt) from @dtlist

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 16, 2010 at 1:20 pm

    #1181255

    Thanks! How do I add numeric values to this now?

  • June 16, 2010 at 1:25 pm

    #1181257

    assuming you want them in different columns then it would be something like this.

    declare @dtlist table

    (dt datetime)

    declare @x int

    set @x = 0

    while @x < 12

    begin

    insert into @dtlist select dateadd(mm,@x,'01/01/10')

    set @x=@x+1

    end

    select

    datename(month,dt)as dtname,

    datepart(mm,dt) as mmpart

    from @dtlist

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 17, 2010 at 6:47 am

    #1181518

    -- The XML way and current connection language setting

    SELECTm.i.value('(text()[1])', 'VARCHAR(20)') AS theMonthName,

    ROW_NUMBER() OVER (ORDER BY m.i) AS theMonthNumber

    FROM(

    SELECTCAST('<m>' + REPLACE(months, ',', '</m><m>') + '</m>' AS XML).query('(.)')

    FROMsys.syslanguages

    WHERElangid = @@LANGID

    ) AS d(m)

    CROSS APPLYd.m.nodes('m') AS m(i)

    N 56°04'39.16"
    E 12°55'05.25"

  • June 17, 2010 at 7:23 am

    #1181544

    Here's another way to do this:

    declare @StartDate datetime

    -- get the 12/1 date for the previous year

    set @StartDate = DateAdd(month, -1, DateAdd(year, DATEDIFF(year, 0, GetDate()), 0))

    -- See Jeff Moden's article

    -- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

    -- at http://www.sqlservercentral.com/articles/T-SQL/62867/.

    -- NOTE! A permanent tally table will always be MUCH faster

    -- than this inline one. See the above article to create your own!

    ;WITH

    Tens (N) AS (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 UNION ALL SELECT 0),

    Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Tens t1 CROSS JOIN Tens t2)

    SELECT MonthNbr = N,

    MonthName = DATENAME(month, DateAdd(month, N, @StartDate))

    FROM Tally

    WHERE N between 1 and 12

    ORDER BY N

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 18, 2010 at 6:29 am

    #1182073

    iklektic (6/16/2010)

    Hi everyone! I build a lot of reports based on months and years. I would love to be able to have a query stored where I can quickly call on to obtain a list of months for my Parameters.

    I can't figure this out!! Does anyone have a query they call on to get a list of the months of the year?

    Are these parameters for a Reporting Services report?

    Greg E

  • June 18, 2010 at 8:09 am

    #1182149

    Yes, it is for reporting services.

    ~ iklektic

  • June 18, 2010 at 8:15 am

    #1182152

    Isn't there a date picker / calendar functionality built right in that you could use?

    Greg E

  • June 18, 2010 at 8:21 am

    #1182157

    There is a calendar that I use frequently. However, some reports I use are based on an entire months and I was trying to find a way to get the parameters quicker.

  • June 18, 2010 at 11:28 am

    #1182281

    Wouldn't it just be easier to make a user function or a reference table?

  • June 18, 2010 at 11:40 am

    #1182290

    For one to 12? Rounds trip to the server? Wouldn't it be easier to hard code these in? AFAIK, months don't change often.

  • June 18, 2010 at 11:51 am

    #1182296

    You're right, I hadn't considered that consequence.

    Thank you.

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

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