SQL Query to return each date of month in each different row

Sometimes we need a resultset in SQL that should list all dates of a particular month.
For example, you want to return a resultset containing total of all expenses done against each date. But for that your expense table not necessary to contain entry of each and every date.
There may be some dates for which there is no expense, and simply that date will not be in the expense table.
In such scenarios you would always need to have one resultset (virtual table) containing list of all dates, which you can use to join (Left Join, ideally) with your expenses table by dates to get your desired result.

This is possible using CTE by performing recursive SELECT.

Following is the complete SQL Query to generate list of all dates for a particular month:



Image: Resultset after executing below SELECT Query
P.S. - There are actually 31 rows, its just due to screen truncate it showing 27 in image.


DECLARE @MonthName VARCHAR(3)
DECLARE @Year VARCHAR(4)
DECLARE @MaxDay INT

SET @MonthName = 'MAR'
SET @Year = '2012'
SET @MaxDay = 31;

WITH CTE_MonthDays AS (
SELECT 1 AS fldDay,
CONVERT(datetime, '1/' + @MonthName + '/' + @Year) AS fldDate
UNION ALL
SELECT fldDay + 1,
CONVERT(Datetime, CONVERT(varchar(2), fldDay + 1) + '/' + @MonthName + '/' + @Year) AS fldDate
FROM CTE_MonthDays
WHERE
fldDay + 1 <= @MaxDay
)

SELECT fldDate FROM CTE_MonthDays