====== ExplodeDate ======
  * function, which returns days between given range as a table,
  * uses CTE, no additional tables needed

** Sql statetement **  
<code sql>
WITH DatesTable AS
(
  SELECT CONVERT(datetime,'2015-01-01') AS [Date]
  UNION ALL
  SELECT  DATEADD(DAY,1,[Date])
  FROM    DatesTable   
  WHERE   DATEADD(DAY,1,[Date]) <= CONVERT(datetime,'2015-09-01')
)
SELECT [Date] FROM DatesTable
OPTION (MAXRECURSION 1000);
</code>
  
** Function**
<code sql>
-- =============================================
-- Author:	Tomas Borovicka
-- Create date: <Create Date,,>
-- Description:	Returns days between given range as a table.
-- =============================================
CREATE FUNCTION [dbo].[ExplodeDates] (@dateFrom datetime, @dateTo datetime)
RETURNS
@DateList table
(
    [Date] datetime
)
AS
BEGIN

WITH DatesTable AS
(
  SELECT @dateFrom AS [Date]
  UNION ALL
  SELECT  DATEADD(DAY,1,[Date])
  FROM    DatesTable   
  WHERE   DATEADD(DAY,1,[Date]) < @dateTo
)

INSERT INTO @DateList
        ([Date])
        (
		SELECT  [Date]
		FROM    DatesTable
		)
OPTION (MAXRECURSION 0);
RETURN

END
</code>

** Example **
<code sql>
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = CONVERT(datetime,'1.1.2011',104);
SET @dateTo = CONVERT(datetime,'10.1.2011',104);

SELECT * FROM [DM_Health].[dbo].[ExplodeDates] (@dateFrom,@dateTo)
</code>

** Result **
<code>
Date
-----------------------
2011-01-01 00:00:00.000
2011-01-02 00:00:00.000
2011-01-03 00:00:00.000
2011-01-04 00:00:00.000
2011-01-05 00:00:00.000
2011-01-06 00:00:00.000
2011-01-07 00:00:00.000
2011-01-08 00:00:00.000
2011-01-09 00:00:00.000

(9 row(s) affected)
</code>

** Example 2 **
<code sql>
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],
    DATEPART(dw, [Date]) AS [DayOfWeek],
    DATENAME(dw, [Date]) AS [DayOfWeekName],
    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 [DatesTable] OPTION (MaxRecursion 10000)

</code>

**Result 2 **
<code> 
ID                             Date                    Day         DaySuffix                        DayOfWeek   DayOfWeekName                  DayOfYear   WeekOfYear  WeekOfMonth Month       MonthName                      Quarter     QuarterName Year
------------------------------ ----------------------- ----------- -------------------------------- ----------- ------------------------------ ----------- ----------- ----------- ----------- ------------------------------ ----------- ----------- -----------
20150101                       2015-01-01 00:00:00.000 1           1st                              5           Thursday                       1           1           1           1           January                        1           First       2015
20150102                       2015-01-02 00:00:00.000 2           2nd                              6           Friday                         2           1           1           1           January                        1           First       2015
20150103                       2015-01-03 00:00:00.000 3           3rd                              7           Saturday                       3           1           1           1           January                        1           First       2015
20150104                       2015-01-04 00:00:00.000 4           4th                              1           Sunday                         4           2           2           1           January                        1           First       2015
20150105                       2015-01-05 00:00:00.000 5           5th                              2           Monday                         5           2           2           1           January                        1           First       2015
20150106                       2015-01-06 00:00:00.000 6           6th                              3           Tuesday                        6           2           2           1           January                        1           First       2015
20150107                       2015-01-07 00:00:00.000 7           7th                              4           Wednesday                      7           2           2           1           January                        1           First       2015
20150108                       2015-01-08 00:00:00.000 8           8th                              5           Thursday                       8           2           2           1           January                        1           First       2015
20150109                       2015-01-09 00:00:00.000 9           9th                              6           Friday                         9           2           2           1           January                        1           First       2015

(9 row(s) affected)

</code>