--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
SQL Calendar Table
最新推荐文章于 2024-08-09 07:32:23 发布