dynamic view for analysis services date dimension

dates.sql

-- Table-valued function to return a list of dates via a recursive query

CREATE FUNCTION [dbo].[dyn_dates](@DateFrom datetime, @DateTo datetime)
RETURNS @table TABLE (date_day datetime)
AS
BEGIN
  WITH dates AS (
    SELECT date_day = @DateFrom
     UNION ALL
    SELECT DATEADD(dd, 1, dates.date_day) 
      FROM dates
     WHERE DATEADD(dd, 1, dates.date_day) <= @DateTo
  ) INSERT INTO @table(date_day)
    SELECT date_day from dates OPTION (MAXRECURSION 0)

  RETURN
END
GO

-- View to be accessed from analysis services
-- @DateFrom / @DateTo could be calculated from oldest/newest value available

CREATE VIEW [dbo].[view_dates] AS
SELECT * FROM [dbo].[dyn_dates](
  datefromparts(year(getdate())-5,1,1),
  datefromparts(year(getdate()),12,31)
)
GO

-- could  be enhanced with calculated fields for year/month/... hierarchy
-- formatting fields as name columns for each hiearchy since date formatting in analysis services is somehow broken for me...
CREATE VIEW [dbo].[view_dates] AS
SELECT date_day,
       RIGHT('0' + datename(dd, date_day), 2) + '.' + 
	   RIGHT('0' + cast(month(date_day) AS NVARCHAR(2)), 2) + '.' +
	   datename(yy, date_day) AS str_day,
       datefromparts(year(date_day), 1, 1) AS date_year,
	   datename(yy, date_day) AS str_year,
       datefromparts(year(date_day), month(date_day), 1) AS date_month,
	   datename(mm, date_day) + ' ' + datename(yy, date_day) AS str_month,
       datefromparts(year(date_day), datepart(q, date_day) * 3 - 2, 1) AS date_quarter,
	   'Q' + datename(q, date_day) + ' ' + datename(yy, date_day) AS str_quarter
  FROM [dbo].[dyn_dates](datefromparts(year(getdate())-5,1,1), datefromparts(year(getdate()),12,31))