SQL Server Performance

Getting a list of days of a given month

Discussion in 'SQL Server 2005 General Developer Questions' started by EMoscosoCam, Nov 16, 2008.

  1. EMoscosoCam Member

    Hello
    How can I get a resultset with a single column where every row is a Date from a given month from start day to the end? All of this using a single SELECT statement.
    For example, if the chosen month is October, then the list would be '01-10-2008', '02-10-2008',..., '31-10-2008'.
    Thanks a lot.
  2. MohammedU New Member

  3. Madhivanan Moderator

    [quote user="EMoscosoCam"]
    Hello
    How can I get a resultset with a single column where every row is a Date from a given month from start day to the end? All of this using a single SELECT statement.
    For example, if the chosen month is October, then the list would be '01-10-2008', '02-10-2008',..., '31-10-2008'.
    Thanks a lot.
    [/quote]declare @month int
    select
    @month=10select
    dateadd(month,@month-1,dateadd(year,datediff(year,0,getdate()),0))+numberfrom
    master..spt_values where
    type='p' and number< day(dateadd(month,@month,0)-1)
  4. TommCatt New Member

    The spt_values table is largely undocumented and is not guaranteed to be around in the "next version" of Sql Server. It did make it to SS2005 but I haven't checked SS2008.This is an excellent example of where a Tally table comes in handy. I won't go into what a Tally table is, you can google it if you need more info. Just understand that I start my Tally table at 0 instead of 1. If you don't already have a Tally table, I've included code to create one as a table variable. Just uncomment it and change the FROM clause to use it.I also have a function that returns a datetime value from integer year, month, and day values. The problem with using that in this particular instance is that you have to know in advance how many days you want to generate. That's not difficult to do -- it's just an extra step that we are able to avoid this way.
    --declare @Tally table(-- NZ int--);----declare @Value int;--set @Value = 0;----while @Value < 50 begin-- insert @Tally( NZ )-- values( @Value );-- set @Value = @Value + 1;--end;declare @TestDate datetime;set @TestDate = '2008-12-01'; -- set to the first day of the desired monthselect DateAdd( dd, Tally.ZN, @TestDate ) as DayOfMonthfrom Utility.dbo.Tally Tallywhere Tally.ZN < 31 -- Limit the range of the scan (allows for index seek) and Tally.ZN < Day( DateAdd( dd, Tally.ZN, @TestDate )); -- omit if it wraps around to the next month
  5. elsuket New Member

    Hello,
    You can also try with a CTE :
    DECLARE @month TINYINT
    SET @month = 1;

    WITH
    CTE_Days AS
    (
    SELECT DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM CTE_Days
    WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))))))
    )
    SELECT D
    FROM CTE_Days
  6. Madhivanan Moderator

    [quote user="elsuket"]
    Hello,
    You can also try with a CTE :
    DECLARE @month TINYINT
    SET @month = 1;

    WITH
    CTE_Days AS
    (
    SELECT DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM CTE_Days
    WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))))))
    )
    SELECT D
    FROM CTE_Days
    [/quote]
    More simplyDECLARE @month TINYINT
    SET
    @month = 1;WITHCTE_Days
    AS
    (SELECT dateadd(month,@month-1,dateadd(year,datediff(year,0,getdate()),0)) D
    UNION ALL
    SELECT DATEADD(day, 1, D)FROM CTE_Days
    WHERE D < dateadd(month,@month,dateadd(year,datediff(year,0,getdate()),0))-1
    )SELECT
    DFROM
    CTE_Days
  7. Jeff Moden New Member

    Whether you use a Tally table, spt_values, or a CTE, if you don't know how it works, it will be a limited tool for you. There is an article that explains the fundamentals of a Tally table and counting CTE or cteTally here...
    http://www.sqlservercentral.com/articles/T-SQL/62867/
  8. FrankKalis Moderator

    [quote user="Jeff Moden"]
    Whether you use a Tally table, spt_values, or a CTE, if you don't know how it works, it will be a limited tool for you. There is an article that explains the fundamentals of a Tally table and counting CTE or cteTally here...
    http://www.sqlservercentral.com/articles/T-SQL/62867/
    [/quote]
    ...what a shameless plug this is! [:D][:p]
    Seriously though it's a good article. Sometimes we forget to explain the "why" while focussing on the "how" and there are far more non-expert users than experts out there and even less true experts. [:)]
  9. Jeff Moden New Member

    Heh... Dang... Long time no see, Frank. I forgot you were a moderator on this fine forum and it's good to see the familiar shark face smile of your avatar. Thanks for the good "review" on the article.
  10. Sandy New Member

    Hi Jeff,
    Nice to see you here....[:)].....Hope you'll guide us to new concepts of SQL here too...
    good article...jeff..
    Thank you,
    Sandy.
  11. Madhivanan Moderator

    Well. master..spt_values is available in SQL Server 2008 too
  12. Sandeep Mittal New Member

  13. FrankKalis Moderator

    Thanks for the link!
    However, note that this thread you replied to is from 2009. :)

Share This Page