比如查询2011-01-02 到2011-02-15日期
1、存错过程
USE[TEST]
IF OBJECT_ID('PRO_ADDDATE') IS NOT NULL
DROP PROCEDURE PRO_ADDDATE
GO
CREATE PROCEDURE PRO_ADDDATE
(@STARTTIME DATE,@ENDTIME DATE)
AS
BEGIN
IF EXISTS(SELECT * FROM sys.tables WHERE name = '#T_TEST1')
DROP TABLE #T_TEST1
CREATE TABLE #T_TEST1(INPUTDAY DATE)
insert #T_TEST1(INPUTDAY) select @starttime
WHILE @STARTTIME<@ENDTIME
BEGIN
--SELECT DATEADD(DAY,1,@STARTTIME)AS INPUTDAY INTO #T_TEST1
INSERT #T_TEST1(INPUTDAY) SELECT DATEADD(DAY,1,@STARTTIME)
SET @STARTTIME = DATEADD(DAY,1,@STARTTIME)
END
SELECT * FROM #T_TEST1
drop table #T_TEST1
END
2、动态SQL
CREATE TABLE #T1 (DAT DATE)
DECLARE @STARTTIME DATE
DECLARE @ENDTIME DATE
SET @STARTTIME='2011-11-11'
SET @ENDTIME='2011-11-21'
WHILE @STARTTIME<@ENDTIME
BEGIN
INSERT #T1(DAT) SELECT DATEADD(DAY,1,@STARTTIME)
SET @STARTTIME = DATEADD(DAY,1,@STARTTIME)
END
SELECT * FROM #T1
DROP TABLE #T1
3、不用任何表,直接输出
DECLARE @STARTTIME DATE
DECLARE @ENDTIME DATE
SET @STARTTIME='2011-11-11'
SET @ENDTIME='2011-11-21'
WHILE @STARTTIME<@ENDTIME
BEGIN
print @STARTTIME
set @STARTTIME = DATEADD(DAY,1,@STARTTIME)
END