需要帮助来修改我的SQL代码

I wanted to generate datetime ranges in SQL Server 2000. I want something that will be compatible with 2000, 2005 and 2008. If its compatible with 2012, that would be great too.

我想在SQL Server 2000中生成日期时间范围。我想要与2000,2005和2008兼容的东西。如果它与2012兼容,那也会很棒。

So, I found the answer here - Generate Dates between date ranges . But, I want two columns instead of 1. I want a start date column and an end date column. How do I modify the query below to get it ?

所以,我在这里找到了答案 - 在日期范围之间生成日期。但是,我想要两列而不是1.我想要一个开始日期列和一个结束日期列。如何修改下面的查询才能获得它?

-- Declarations

DECLARE @dates TABLE(dt datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END

-- Output

SELECT * FROM @dates

---

My code -

我的代码 -

DECLARE @dates TABLE(StartDate datetime, EndDate datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom, @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END

SELECT * FROM @dates

Error - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

错误 - 为变量赋值的SELECT语句不能与数据检索操作结合使用。

3 个解决方案

#1

Here's a set-based solution that uses spt_values, a system table found in all versions of SQL Server from 2000 -> 2014. In 2000, this will be limited to a date range of 256 days. In more modern versions, this upper limit will be 2048.

这是一个基于集合的解决方案,它使用spt_values,这是一个从2000年到2014年在所有SQL Server版本中找到的系统表。在2000年,这将限制在256天的日期范围内。在更现代的版本中,这个上限将是2048。

Typically a set-based solution is going to work better than any type of WHILE loop.

通常,基于集合的解决方案比任何类型的WHILE循环都更好。

DECLARE @start DATETIME, @end DATETIME;

SELECT @start = '20010101', @end = '20010112';

-- INSERT @dates(StartDate, EndDate)
SELECT 
  StartDate = DATEADD(DAY, number,   @start), 
  EndDate   = DATEADD(DAY, number+1, @start)
FROM master..spt_values 
WHERE [type] = 'p' 
AND number < DATEDIFF(DAY, @start, @end);

That said, and particularly given the 256-day limitation, you should just consider a numbers and/or calendar table of your own to get around this limitation:

也就是说,特别是考虑到256天的限制,您应该考虑自己的数字和/或日历表来解决这个限制:

http://www.sqlperformance.com/generate-a-set-1

http://www.sqlperformance.com/generate-a-set-2

http://www.sqlperformance.com/generate-a-set-3

So you could create one using the following code (assuming that 100,000 days is a large enough range for you):

所以你可以使用以下代码创建一个(假设100,000天是一个足够大的范围):

SELECT TOP 100000 Number = IDENTITY(INT,1,1) 
INTO dbo.Numbers 
FROM sysobjects AS s1 
CROSS JOIN sysobjects AS s2
ORDER BY 1;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number);

The above took all of 4.2 seconds on my 2000 system. With that table created, the query becomes even easier (and far less restrictive):

在我的2000系统上,上面花了4.2秒。创建该表后,查询变得更加容易(并且限制性更小):

DECLARE @start DATETIME, @end DATETIME;

SELECT @start = '20010101', @end = '20010112';

-- INSERT @Dates(StartDate, EndDate)
SELECT 
  StartDate = DATEADD(DAY, Number-1, @start), 
  EndDate   = DATEADD(DAY, Number,   @start)
FROM dbo.Numbers 
WHERE Number <= DATEDIFF(DAY, @start, @end); 

#2

this uses a numbers table:

这使用数字表:

DECLARE @dateFrom datetime, @dateTo datetime

SELECT @dateFrom = '2001/01/01'
      ,@dateTo = '2001/01/12'

select
    @dateFrom+Number-1
    From Numbers
    WHERE Number<=datediff(day,@dateFrom,@dateTo+1)

OUTPUT:

-----------------------
2001-01-01 00:00:00.000
2001-01-02 00:00:00.000
2001-01-03 00:00:00.000
2001-01-04 00:00:00.000
2001-01-05 00:00:00.000
2001-01-06 00:00:00.000
2001-01-07 00:00:00.000
2001-01-08 00:00:00.000
2001-01-09 00:00:00.000
2001-01-10 00:00:00.000
2001-01-11 00:00:00.000
2001-01-12 00:00:00.000

(12 row(s) affected)

I'm unsure what "2" columns the OP wants, but it is easy in the query:

我不确定OP想要什么“2”列,但在查询中很容易:

select
    @dateFrom+Number-1,@dateFrom+Number
    From Numbers
    WHERE Number<=datediff(day,@dateFrom,@dateTo)

OUTPUT:

----------------------- -----------------------
2001-01-01 00:00:00.000 2001-01-02 00:00:00.000
2001-01-02 00:00:00.000 2001-01-03 00:00:00.000
2001-01-03 00:00:00.000 2001-01-04 00:00:00.000
2001-01-04 00:00:00.000 2001-01-05 00:00:00.000
2001-01-05 00:00:00.000 2001-01-06 00:00:00.000
2001-01-06 00:00:00.000 2001-01-07 00:00:00.000
2001-01-07 00:00:00.000 2001-01-08 00:00:00.000
2001-01-08 00:00:00.000 2001-01-09 00:00:00.000
2001-01-09 00:00:00.000 2001-01-10 00:00:00.000
2001-01-10 00:00:00.000 2001-01-11 00:00:00.000
2001-01-11 00:00:00.000 2001-01-12 00:00:00.000

if you need to create a numbers table, see: What is the best way to create and populate a numbers table?

如果您需要创建数字表,请参阅:创建和填充数字表的最佳方法是什么?

#3

-- Declarations

DECLARE @dates TABLE(dt datetime, dt2 datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
DECLARE @endDate datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   SELECT @endDate = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom, @endDate
END

-- Output

SELECT * FROM @dates