获取两个日期之间的每一天

我只把我现在用的方法列出来:

1.建立一个辅助表,里面只有一列,内容为0-100W个数

CREATE TABLE Nums(n INT NOT NULL PRIMARY KEY);
insert into Nums values (0)
DECLARE @max AS INT,@rc AS INT;
SET @max = 1000000;
SET @rc=1;
INSERT INTO Nums VALUES (1);
WHILE @rc * 2 <=@max
  BEGIN
      INSERT INTO Nums SELECT n+@rc FROM Nums;
      SET @rc=@rc * 2;
  END
  
INSERT INTO Nums SELECT n +@rc FROM Nums WHERE n+@rc<=@max ;  

二.开始计算两个日期之间的各个天数

DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate='2008-02-21';
SET @EndDate ='2013-05-12';

--SELECT @StartDate + n FROM (SELECT DATEDIFF(DAY,@StartDate,@enddate) dday) num ,dbo.Nums_Assist WHERE 
--num.dday>=dbo.Nums_Assist.n

SELECT DATEADD(DAY,n,@startdate)  FROM (SELECT DATEDIFF(DAY,@StartDate,@enddate) dday) num ,dbo.Nums WHERE 
num.dday>=dbo.Nums.n

两种方式都可以使用

 

转载于:https://www.cnblogs.com/cgdou/archive/2013/01/12/2857314.html