pietlinden (1/15/2014)

---

Can you just create it as a table-valued function?

CREATE FUNCTION [dbo].[GenerateCalendar]

(

@FromDate DATETIME,

@NoDays INT

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)

--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)

WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows

E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows

E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows

cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

SELECT [SeqNo] = t.N,

[Date] = dt.DT,

[Year] = dp.YY,

[YrNN] = dp.YY % 100,

[YYYYMM] = dp.YY * 100 + dp.MM,

[BuddhaYr] = dp.YY + 543,

[Month] = dp.MM,

[Day] = dp.DD,

[WkDNo] = DATEPART(dw,dt.DT),

[WkDName] = CONVERT(NCHAR(9),dp.DW),

[WkDName2] = CONVERT(NCHAR(2),dp.DW),

[WkDName3] = CONVERT(NCHAR(3),dp.DW),

[JulDay] = dp.DY,

[JulWk] = dp.DY/7+1,

[WkNo] = dp.DD/7+1,

[Qtr] = DATEPART(qq,dt.Dt),

[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,

[LdOfMo] = DATEPART(dd,dp.LDtOfMo),

[LDtOfMo] = dp.LDtOfMo

FROM cteTally t

CROSS APPLY ( --=== Create the date

SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)

) dt

CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"

-- (Cascading CROSS APPLY, Acourtesy of ChrisM)

SELECT YY = DATEPART(yy,dt.DT),

MM = DATEPART(mm,dt.DT),

DD = DATEPART(dd,dt.DT),

DW = DATENAME(dw,dt.DT),

Dy = DATEPART(dy,dt.DT),

LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp

GO

And then query it like a table?

SELECT *

FROM dbo.GenerateCalendar(GETDATE(),10);