</pre><pre name="code" class="sql">IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE
)
GO
INSERT TBL
SELECT '2014-09-30' UNION ALL
SELECT '2014-10-06'
go
DECLARE @DATE DATE
SELECT @DATE=MAX(日期) FROM TBL
;WITH T
AS(
SELECT * FROM TBL
UNION ALL
SELECT DATEADD(DD,1,A.日期) FROM T A
WHERE NOT EXISTS(SELECT * FROM TBL B
WHERE B.日期=DATEADD(DD,1,A.日期)
)
AND A.日期<@DATE
)
SELECT *FROM T ORDER BY 日期
CREATE FUNCTION F_TEST1
(
@BEGIN_DATE DATETIME,
@END_DATE DATETIME
)
RETURNS @T TABLE(DATE DATETIME)
AS
BEGIN
WITH ETC AS
(
SELECT @BEGIN_DATE AS DATE
UNION ALL
SELECT DATE+1 FROM ETC
WHERE DATE+1 <=@END_DATE
)
INSERT INTO @T
SELECT * FROM ETC OPTION(MAXRECURSION 0);
RETURN
END
GO
--测试示例
SELECT * FROM DBO.F_TEST1('2009-01-01','2009-01-10')
GO
--创建函数
CREATE FUNCTION F_TEST2
(
@BEGIN_DATE DATETIME,
@END_DATE DATETIME
)
RETURNS @T TABLE(DATE DATETIME)
AS
BEGIN
INSERT INTO @T
SELECT DATEADD(DD,NUMBER,@BEGIN_DATE) AS DATE
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND DATEADD(DD,NUMBER,@BEGIN_DATE)<=@END_DATE
RETURN
END
GO
--测试示例
SELECT * FROM DBO.F_TEST2('2009-01-01','2009-01-10')