CREATE FUNCTION GenerateCalendar ( @FromDate DATETIME, @NoDays INT )RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== 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) ) dpGOCREATE TABLE #Calendar ([Date] DATETIME PRIMARY KEY ,[Year] INT ,[JulWk] INT ,[JulDay] INT ,ISO_WK AS (CAST([Year] AS CHAR(4)) + 'W' + CAST([JulWk] AS VARCHAR(2)) + '-' + CAST([JulDay] AS VARCHAR(3))) )INSERT INTO #CalendarSELECT [Date], [Year], [JulWk], [JulDay]FROM GenerateCalendar('20120101', 365*2)SELECT * FROM #CalendarWHERE ISO_WK LIKE '2012W26-[67]'DROP TABLE #Calendar

CREATE TABLE #Calendar ([Date] DATETIME PRIMARY KEY ,[Year] INT ,[JulWk] INT ,[WkDNo] INT ,ISO_WK AS (CAST([Year] AS CHAR(4)) + 'W' + CAST([JulWk] AS VARCHAR(2)) + '-' + CAST([WkDNo] AS VARCHAR(3))) )INSERT INTO #CalendarSELECT [Date], [Year], [JulWk], [WkDNo]FROM GenerateCalendar('20120101', 365*2)SELECT * FROM #CalendarWHERE ISO_WK LIKE '2012W26-[67]'