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

How to get the dates between two dates?

如何得到两个日期之间的日期?

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.

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

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

Compilation Error

编译错误

Incorrect syntax near the keyword 'SET'.

在关键字“SET”附近的语法错误。

How to solve this.

如何解决这个问题。

Thanks in advance

谢谢提前

7 个解决方案

#1

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;

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:

如果你不想，或者不能创建一个日历表，你仍然可以在不使用递归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:

如需进一步阅读，请参阅:

Generate a set or sequence without loops – part 1
生成一个没有循环的集合或序列-第1部分
Generate a set or sequence without loops – part 2
生成一个没有循环的集合或序列-第2部分
Generate a set or sequence without loops – part 3
生成一个没有循环的集合或序列-第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.

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

So with your data:

所以与你的数据:

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

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:

要得到28-04-2014的数量(我猜是你的要求)，你实际上不需要以上任何一种，你可以使用:

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;

#2

You can use this script to find dates between two dates. Reference taken from this Article:

您可以使用此脚本查找两个日期之间的日期。引用本文:

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = '2015-01-01'
SET @EndDateTime = '2015-01-12';

WITH DateRange(DateData) AS 
(
    SELECT @StartDateTime as Date
    UNION ALL
    SELECT DATEADD(d,1,DateData)
    FROM DateRange 
    WHERE DateData < @EndDateTime
)
SELECT DateData
FROM DateRange
OPTION (MAXRECURSION 0)
GO

#3

create procedure [dbo].[p_display_dates](@startdate datetime,@enddate datetime)
as
begin
    declare @mxdate datetime
    declare @indate datetime
    create table #daterange (dater datetime)
    insert into #daterange values (@startdate)
    set @mxdate = (select MAX(dater) from #daterange)
    while @mxdate < @enddate
        begin
            set @indate = dateadd(day,1,@mxdate)
            insert into #daterange values (@indate)
            set @mxdate = (select MAX(dater) from #daterange)
        end
    select * from #daterange
end

#4

Easily create a Table Value Function that will return a table with all dates. Input dates as string You can customize the date in the the format you like '01/01/2017' or '01-01-2017' in string formats (103,126 ...)

轻松创建一个表值函数，该函数将返回一个包含所有日期的表。输入日期作为字符串，您可以自定义日期的格式，您喜欢“01/01/2017”或“01-01-2017”在字符串格式(103,126…)

Try this

试试这个

CREATE FUNCTION [dbo].[DateRange_To_Table] ( @minDate_Str NVARCHAR(30), @maxDate_Str NVARCHAR(30))

RETURNS  @Result TABLE(DateString NVARCHAR(30) NOT NULL, DateNameString NVARCHAR(30) NOT NULL)

AS

begin

    DECLARE @minDate DATETIME, @maxDate DATETIME
    SET @minDate = CONVERT(Datetime, @minDate_Str,103)
    SET @maxDate = CONVERT(Datetime, @maxDate_Str,103)


    INSERT INTO @Result(DateString, DateNameString )
    SELECT CONVERT(NVARCHAR(10),@minDate,103), CONVERT(NVARCHAR(30),DATENAME(dw,@minDate))



    WHILE @maxDate > @minDate
    BEGIN
        SET @minDate = (SELECT DATEADD(dd,1,@minDate))
        INSERT INTO @Result(DateString, DateNameString )
        SELECT CONVERT(NVARCHAR(10),@minDate,103), CONVERT(NVARCHAR(30),DATENAME(dw,@minDate))
    END




    return

end

To execute the function do this:

要执行此函数，请执行以下操作:

SELECT * FROM dbo.DateRange_To_Table ('01/01/2017','31/01/2017')

The output will be

输出将

01/01/2017  Sunday
02/01/2017  Monday
03/01/2017  Tuesday
04/01/2017  Wednesday
05/01/2017  Thursday
06/01/2017  Friday
07/01/2017  Saturday
08/01/2017  Sunday
09/01/2017  Monday
10/01/2017  Tuesday
11/01/2017  Wednesday
12/01/2017  Thursday
13/01/2017  Friday
14/01/2017  Saturday
15/01/2017  Sunday
16/01/2017  Monday
17/01/2017  Tuesday
18/01/2017  Wednesday
19/01/2017  Thursday
20/01/2017  Friday
21/01/2017  Saturday
22/01/2017  Sunday
23/01/2017  Monday
24/01/2017  Tuesday
25/01/2017  Wednesday
26/01/2017  Thursday
27/01/2017  Friday
28/01/2017  Saturday
29/01/2017  Sunday
30/01/2017  Monday
31/01/2017  Tuesday

#5

Just saying...here is a more simple approach to this:

只是说……这里有一个更简单的方法:

declare @sdate date = '2017-06-25'
    , @edate date = '2017-07-24'

; with dates_CTE (date) as (
        select @sdate 
    Union ALL
        select DATEADD(day, 1, date)
        from dates_CTE
        where date < @edate
) select 
    *
from dates_CTE

#6

I listed dates of 2 Weeks later. You can use variable @period OR function datediff(dd, @date_start, @date_end)

我列出了两周后的日期。可以使用变量@period或函数datediff(dd， @date_start， @date_end)

declare @period INT, @date_start datetime, @date_end datetime, @i int;

set @period = 14
set @date_start = convert(date,DATEADD(D, -@period, curent_timestamp))
set @date_end = convert(date,current_timestamp)
set @i = 1

create table #datesList(dts datetime)
insert into #datesList values (@date_start)
while @i <= @period
    Begin
        insert into #datesList values (dateadd(d,@i,@date_start))
        set @i = @i + 1
    end
select cast(dts as DATE) from #datesList
Drop Table #datesList

#7

This is the method that I would use.

这就是我要用的方法。

DECLARE 
    @DateFrom DATETIME = GETDATE(),
    @DateTo DATETIME = DATEADD(HOUR, -1, GETDATE() + 2); -- Add 2 days and minus one hour


-- Dates spaced a day apart 

WITH MyDates (MyDate)
AS (
    SELECT @DateFrom
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate)
    FROM MyDates
    WHERE MyDate < @DateTo
   )

SELECT 
    MyDates.MyDate
    , CONVERT(DATE, MyDates.MyDate) AS [MyDate in DATE format]
FROM 
    MyDates;

Here is a similar example, but this time the dates are spaced one hour apart to further aid understanding of how the query works:

这里有一个类似的例子，但是这次日期间隔了一个小时，以进一步帮助理解查询是如何工作的:

-- Alternative example with dates spaced an hour apart

WITH MyDates (MyDate)
AS (SELECT @DateFrom
    UNION ALL
    SELECT DATEADD(HOUR, 1, MyDate)
    FROM MyDates
    WHERE MyDate < @DateTo
   )

SELECT 
    MyDates.MyDate
FROM 
    MyDates;

As you can see, the query is fast, accurate and versatile.

如您所见，查询是快速、准确和通用的。

#1