ExplodeDate

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

Sql statetement

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);

Function

-- =============================================
-- 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

Example

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)

Result

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)

Example 2

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)

Result 2

 
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)