SQL Server Performance Forum – Threads Archive

Getting a list of days of a given month

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.

---

Check the following artilce and code…
it will help you to write your own code…
http://weblogs.sqlteam.com/jeffs/archive/2003/12/09/646.aspx

---

[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)

---

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

---

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 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

---

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 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! [][]
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. []

---

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.

---

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.

---

Well. master..spt_values is available in SQL Server 2008 too

---

Refer this link
http://itdeveloperzone.blogspot.in/2012/11/generate-list-of-dates-in-sql-server.html

---

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

---

]]>