SQL Calendar Table

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118

--SQL Server 2000 -----Calendar Table Geovin Du 涂聚文   declare @start datetime, @end datetime   set @start = '2006-01-01' set @end = '2006-05-02'   declare @no_of_Days int set @no_of_days = datediff(dd,@start,@end) + 1 set rowcount @no_of_days   select identity(int,0,1) as dy into #temp from sysobjects a, sysobjects b   set rowcount 0   select dateadd(dd,dy,@start) as [days] from #temp   drop table #temp -- ---SQL Server 2005 ---Calendar Table WITH CTE_DatesTable AS (   SELECT CAST('20090601' as datetime) AS [date]   UNION ALL   SELECT DATEADD(dd, 1, [date])   FROM CTE_DatesTable   WHERE DATEADD(dd, 1, [date]) <= '20090630' ) SELECT [date] FROM CTE_DatesTable OPTION (MAXRECURSION 0); GO   ---創造日曆函數 Geovin Du 涂聚文 CREATE FUNCTION [dbo].[DateTable] (   @FirstDate datetime,   @LastDate datetime ) RETURNS @datetable TABLE (   [date] datetime ) AS BEGIN     SELECT @FirstDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FirstDate));   SELECT @LastDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @LastDate));   WITH CTE_DatesTable   AS   (     SELECT @FirstDate AS [date]     UNION ALL     SELECT DATEADD(dd, 1, [date])     FROM CTE_DatesTable     WHERE DATEADD(dd, 1, [date]) <= @LastDate   )   INSERT INTO @datetable ([date])   SELECT [date] FROM CTE_DatesTable   OPTION (MAXRECURSION 0)     RETURN END GO ---創造日曆函數 CREATE FUNCTION [dbo].[DatesTable] (   @FirstDate smalldatetime,   @LastDate smalldatetime ) RETURNS @datetable TABLE (   [date] smalldatetime ) AS BEGIN     WITH CTE_DatesTable   AS   (     SELECT @FirstDate AS [date]     UNION ALL     SELECT DATEADD(dd, 1, [date])     FROM CTE_DatesTable     WHERE DATEADD(dd, 1, [date]) <= @LastDate   )   INSERT INTO @datetable ([date])   SELECT [date] FROM CTE_DatesTable   OPTION (MAXRECURSION 0);     RETURN; END GO --測試 SELECT [date] FROM [dbo].[DateTable](GETDATE(), DATEADD(dd,5,GETDATE())) GO SELECT [date] FROM [dbo].[DatesTable](GETDATE(), DATEADD(dd,5,GETDATE())) GO --- create table SQLDatesTable (   id int identity(1,1) not null,   [date] datetime not null ) -- insert into SQLDatesTable ([date]) select [date] from [dbo].[DateTable]('20080101', '20081231') -- Or declare @i int = 0, @date datetime = '20090101' while @i <= 100 begin   insert into SQLDatesTable ([date]) values (dateadd(dd,@i,@date))   set @i = @i + 1 end   select * from SQLDatesTable