获取 SQL Server 中两个日期之间的所有日期

2021-12-02 00:00:00 sql sql-server cursor

如何获取两个日期之间的所有日期?

How to get all 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 dates 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

编译错误

关键字SET"附近的语法不正确.

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 部分
  • 生成一个没有循环的集合或序列——第二部分
  • 生成一个没有循环的集合或序列——第三部分

关于在游标中使用这个日期序列，我真的建议您找到另一种方法.通常有一个基于集合的替代方案会表现得更好.

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;

相关文章