SQL Calendar Table
生活随笔
收集整理的這篇文章主要介紹了
SQL Calendar Table
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
--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---創(chuàng)造日曆函數(shù) 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
---創(chuàng)造日曆函數(shù)
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
轉(zhuǎn)載于:https://www.cnblogs.com/geovindu/archive/2011/08/18/2144253.html
總結(jié)
以上是生活随笔為你收集整理的SQL Calendar Table的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 字符串与字符串函数 - 字符串输出 常
- 下一篇： python基础-2