查询 某个时间段的时间

比如查询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