CTE - Sample

--define limits
DECLARE @todate datetime, @fromdate datetime
SET @fromdate = '2013-01-01'
SET @todate = '2013-12-31'
;WITH DateSequence( [Date] ) AS
(
SELECT @fromdate AS [Date]
UNION ALL
SELECT DATEADD(day, 1, [Date]) FROM DateSequence WHERE DATE < @todate
)

--select result
SELECT
CONVERT(VARCHAR,[Date],112) AS ID,[Date] AS [Date],
DATEPART(DAY,[Date]) AS [Day],
CASE
WHEN DATEPART(DAY,[Date]) = 1 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'st'
WHEN DATEPART(DAY,[Date]) = 2 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'nd'
WHEN DATEPART(DAY,[Date]) = 3 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'th'
END AS [DaySuffix],
DATENAME(dw, [Date]) AS [DayOfWeek],DATEPART(DAYOFYEAR,[Date]) AS [DayOfYear],DATEPART(WEEK,[Date]) AS [WeekOfYear],
DATEPART(WEEK,[Date]) + 1 - DATEPART(WEEK,CAST(DATEPART(MONTH,[Date]) AS VARCHAR) + '/1/' + CAST(DATEPART(YEAR,[Date]) AS VARCHAR)) AS [WeekOfMonth],
DATEPART(MONTH,[Date]) AS [Month],DATENAME(MONTH,[Date]) AS [MonthName],DATEPART(QUARTER,[Date]) AS [Quarter],
CASE DATEPART(QUARTER,[Date])
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS [QuarterName],
DATEPART(YEAR,[Date]) AS [Year]
FROM DateSequence OPTION (MaxRecursion 10000)