在SQL Server中获取两个日期之间的所有日期 [英] Get all dates between two dates in SQL Server

问题描述

如何获取两个日期之间的日期？

How to get the dates between two dates?

我有一个变量 @MAXDATE 最大日期从表。现在我想获得 @Maxdate 和 GETDATE（）之间的所有日期，并希望将这些日期存储在游标中。

I have a variable @MAXDATE which is storing the maximum date from the table. Now I want to get the all dates between @Maxdate and GETDATE() and want to store these date in a cursor.

到目前为止，我已经做了如下：

So far I have done as follows:

;with GetDates As  
(  
select DATEADD(day,1,@maxDate) as TheDate
UNION ALL  
select DATEADD(day,1, TheDate) from GetDates  
where TheDate < GETDATE()  
)  

这是完美的，但当我试图存储游标中的这些值

This is working perfectly but when I am trying to store these values in a cursor

SET @DateCurSor=CURSOR FOR
                SELECT TheDate
                FROM GetDates

编译错误

Incorrect syntax near the keyword 'SET'.

如何解决这个问题。

提前感谢

推荐答案

我的第一个建议是使用您的日历表，如果没有，请创建一个。他们是非常有用的。您的查询就像下面这样简单：

My first suggestion would be use your calendar table, if you don't have one, then create one. They are very useful. Your query is then as simple as:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @MinDate
AND     Date < @MaxDate;

如果您不想或不能创建日历表，在没有递归CTE的情况下：

If you don't want to, or can't create a calendar table you can still do this on the fly without a recursive CTE:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

有关进一步阅读，请参阅：

For further reading on this see:

• 生成集或序列无循环 - 第1部分


• 生成无循环的集合或序列 - 第2部分


• 生成无循环的集或序列 - 第3部分

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

关于在游标中使用这个序列的日期，我真的建议你找另一种方法。

With regard to then using this sequence of dates in a cursor, I would really recommend you find another way. There is usually a set based alternative that will perform much better.

所以，你的数据：

  date   | it_cd | qty 
24-04-14 |  i-1  | 10 
26-04-14 |  i-1  | 20

要获取28-04-2014（我收集是您的要求）实际上不需要上述任何内容，您只需使用：

To get the quantity on 28-04-2014 (which I gather is your requirement), you don't actually need any of the above, you can simply use:

SELECT  TOP 1 date, it_cd, qty 
FROM    T
WHERE   it_cd = 'i-1'
AND     Date <= '20140428'
ORDER BY Date DESC;

如果您不想将其用于特定项目：

If you don't want it for a particular item:

SELECT  date, it_cd, qty 
FROM    (   SELECT  date, 
                    it_cd, 
                    qty, 
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY ic_id 
                                                    ORDER BY date DESC)
            FROM    T
            WHERE   Date  <= '20140428'
        ) T
WHERE   RowNumber = 1;

这篇关于在SQL Server中获取两个日期之间的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！