欢迎访问 生活随笔！
TAG聚合

生活随笔

当前位置： 首页 >

SQL Calendar Table

发布时间：2024/8/26 50 豆豆
生活随笔 收集整理的这篇文章主要介绍了 SQL Calendar Table 小编觉得挺不错的,现在分享给大家,帮大家做个参考.
--SQL Server 2000 -----Calendar Table Geovin Du 涂聚文declare @start datetime, @end datetimeset @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_daysselect identity(int,0,1) as dy into #temp from sysobjects a, sysobjects bset rowcount 0select dateadd(dd,dy,@start) as [days] from #tempdrop table #temp -- ---SQL Server 2005 ---Calendar Table WITH CTE_DatesTable AS (SELECT CAST('20090601' as datetime) AS [date]UNION ALLSELECT DATEADD(dd, 1, [date])FROM CTE_DatesTableWHERE 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 BEGINSELECT @FirstDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FirstDate)); SELECT @LastDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @LastDate));WITH CTE_DatesTableAS(SELECT @FirstDate AS [date]UNION ALLSELECT DATEADD(dd, 1, [date])FROM CTE_DatesTableWHERE DATEADD(dd, 1, [date]) <= @LastDate)INSERT INTO @datetable ([date])SELECT [date] FROM CTE_DatesTableOPTION (MAXRECURSION 0)RETURN END GO ---創造日曆函數 CREATE FUNCTION [dbo].[DatesTable] (@FirstDate smalldatetime,@LastDate smalldatetime ) RETURNS @datetable TABLE ([date] smalldatetime ) AS BEGINWITH CTE_DatesTableAS(SELECT @FirstDate AS [date]UNION ALLSELECT DATEADD(dd, 1, [date])FROM CTE_DatesTableWHERE DATEADD(dd, 1, [date]) <= @LastDate)INSERT INTO @datetable ([date])SELECT [date] FROM CTE_DatesTableOPTION (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 begininsert into SQLDatesTable ([date]) values (dateadd(dd,@i,@date))set @i = @i + 1 endselect * from SQLDatesTable

转载于:https://www.cnblogs.com/geovindu/archive/2011/08/18/2144253.html

总结

以上是生活随笔为你收集整理的SQL Calendar Table的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。