DECLARE @DateInMonth DATETIME
SET @DateInMonth = '20080512'

SELECT DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth), 0) + n0 + n1 + n2
   FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS z0
   CROSS JOIN (SELECT 0 AS n1 UNION SELECT 4 UNION SELECT 8 UNION SELECT 12) AS z1
   CROSS JOIN (SELECT 0 AS n2 UNION SELECT 16 UNION SELECT 32 UNION SELECT 48) AS z2
WHERE DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth), 0) + n0 + n1 + n2 <= DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth)+1, 0)-1

quote:

---

Originally posted by osupratt

I'm trying to build a query that will return all dates within current month. So for this month it would bring in

8/1/2008
8/2/2008
8/3/2008
----
8/31/2008

And then once the month changes; do the same. I searched through the forums and could not find what I'm looking for. Any help is greatly appreciated. Thanks.

---

;
With Date_CTE (Date) AS
(
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

UNION ALL

SELECT DATEADD(d,1,Date)
FROM Date_CTE
WHERE DATEADD(d,1,Date)<DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
)

SELECT * FROM Date_CTE