Get All Dates

When we develop application, we need all the days from the month sometimes. We are thinking of the way to create StoreProcedure to generate days using temp table and cursor. But using cursor will slow down our application process and we want faster way to get all days. So we think of another way to create one table which store all the days from 1-Jan of current year to 31-Dec. That’s also not easy to maintain this table and need to insert all days every year. Actually, I’m using this method in one of my application :D. Don’t try to use this method. This is very risky to get all days and insert all days. Here is very useful query statement to generate days from the month or the given months. I got this statement from one SQL forum but I forgot from which forum and who posted it. Anyway, this statement is really helpful to me and I’m sure you’ll also. Here’s the statement:

DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080301', @NumberOfMonths = 1
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT CalendarDay FROM Days

If you set 2 to @NumberOfMonths variable, you’ll get all the days from 2 months such as Mar and Apr.