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

Question

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

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

到目前为止，我已经完成了以下步骤：

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

这个工作得很完美，但是当我尝试将这些值存储在一个游标中时。

SET @DateCurSor = CURSOR FOR
                SELECT TheDate
                FROM GetDates

编译错误

“SET”关键字附近的语法不正确。

如何解决这个问题？

- user3193557

---

你为什么想要一个光标？尽可能避免使用光标！（WHY on earth和avoid cursors是强调语气的表达方式，翻译时不需要完全照搬，但要保留其强调的意味） - marc_s

情况是这样的，我必须使用CURSOR。 - user3193557

为什么？我99%确定你不需要使用光标！如果你不使用光标会更好！ - marc_s

我有一张表格，其中包含日期、物品代码和数量。 假设该表格的数据如下： 日期 ||物品代码||数量 24-04-14||i-1 ||10 26-04-14||i-1 ||20 现在我该如何获取2014年4月28日的数量？ - user3193557

如果您想展示一张表格，请编辑您的问题并添加它。评论区没有格式化功能。 - Damien_The_Unbeliever

Answer 1

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

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

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

如果您不想或无法创建日历表，则仍然可以通过即时操作而无需使用递归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;

进一步阅读请参见：

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

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

所以，对于您的数据：

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

要获取2014年4月28日的数量（我想这是您的需求），您实际上不需要以上任何内容，只需使用以下代码即可：

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

如果您不想为特定项目使用它：

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;

- GarethD

---

我不确定交叉连接是否必要。在执行查询时似乎与之无关。 - pim

跨连接的必要性在于所需的行数有多少。如果所需日期比sys.all_objects中的对象数量少，则不需要跨连接，但是如果日期范围跨越20年，则需要跨连接。使用“TOP（所需天数）”意味着当需要较少的行时，跨连接几乎没有任何开销，因此将其保留不会造成任何损害。 - GarethD

太棒了！感谢您的解释。非常感激。总体来说，这是一个很棒的查询。 - pim

这是另一篇好的文章：https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ - Jose

Answer 2

您可以使用此脚本查找两个日期之间的日期。 参考自这篇文章：

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

- Anvesh

Answer 3

仅仅是说一下...这里有一个更简单的方法：

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;

- GoldBishop

---

对于那些使用它的人来说，时间和经验已经在我身上留下了印记。CTE对于伪循环非常有用，只要结果集很小（在这个例子中，少于30天；我建议保持少于90天）。任何大于90天的东西，你应该建立一个带有一些可预测日期模式的查找表。刷新一下自己对闰年识别逻辑的理解 ;) - GoldBishop

Answer 4

轻松创建一个表值函数，该函数将返回一个包含所有日期的表格。以字符串形式输入日期。您可以自定义日期格式，比如使用字符串格式（103、126等）中的“01/01/2017”或“01-01-2017”。

试试这个方法

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   

执行该函数，请按照以下步骤进行：

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

输出结果将会是：

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

- Nikos Stasinos

Answer 5

这可以被认为是一种有点棘手的方法，因为在我的情况下，我无法使用CTE表，所以决定与sys.all_objects连接，然后创建行号并将其添加到开始日期，直到达到结束日期。

请参见下面的代码，在其中生成了2018年7月的所有日期。用自己的变量替换硬编码日期（已在SQL Server 2016中测试）：

select top (datediff(dd, '2018-06-30', '2018-07-31')) ROW_NUMBER() 
over(order by a.name) as SiNo, 
Dateadd(dd, ROW_NUMBER() over(order by a.name) , '2018-06-30') as Dt from sys.all_objects a

- rchacko

Answer 6

你可以尝试这个：

    SET LANGUAGE SPANISH

DECLARE @startDate DATE = GETDATE() -- Your start date
DECLARE @endDate DATE = DATEADD(MONTH, 16, GETDATE()) -- Your end date
DECLARE @years INT = YEAR(@endDate) - YEAR(@startDate)

CREATE TABLE #TMP_YEARS (
    [year] INT
)

-- Get all posible years between the start and end date
WHILE @years >= 0
BEGIN
    INSERT INTO #TMP_YEARS
    ([year])
    SELECT YEAR(@startDate) + @years

    SET @years = @years - 1
END

;WITH [days]([day]) AS -- Posible days at a month
(
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL -- days lower than 10
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL -- days lower than 20
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL -- days lower than 30
    SELECT 30 UNION ALL SELECT 31 -- days higher 30
),
[months]([month]) AS -- All months at a year
(
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, d.[day]))) + CONVERT(VARCHAR, d.[day]) as [date]
  FROM #TMP_YEARS a
 CROSS JOIN [months] n -- Join all years with all months
 INNER JOIN [days] d on DAY(EOMONTH(CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + CONVERT(VARCHAR, DAY(EOMONTH(CAST(CONVERT(VARCHAR, a.[year]) + '-' + CONVERT(varchar, n.[month]) + '-15' AS DATE)))))) >= d.[day] AND -- The number of the day can't be higher than the last day of the current month and the current year
                      CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, d.[day]))) + CONVERT(VARCHAR, d.[day]) <= ISNULL(@endDate, GETDATE()) AND -- The current date can't be higher than the end date
                      CONVERT(VARCHAR, a.[year]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, n.[month]))) + CONVERT(VARCHAR, n.[month]) + '-' + REPLICATE('0', 2 - LEN(CONVERT(VARCHAR, d.[day]))) + CONVERT(VARCHAR, d.[day]) >= ISNULL(@startDate, GETDATE()) -- The current date should be higher than the start date
 ORDER BY a.[year] ASC, n.[month] ASC, d.[day] ASC

输出结果将类似于这样，您可以按照自己的喜好格式化日期：

2019-01-24
2019-01-25
2019-01-26
2019-01-27
2019-01-28
2019-01-29
2019-01-30
2019-01-31
2019-02-01
2019-02-02
2019-02-03
2019-02-04
2019-02-05
2019-02-06
2019-02-07
2019-02-08
2019-02-09
...

- Chao Chen

Answer 7

你可以使用SQL Server递归CTE。

DECLARE 
    @MinDate DATE = '2020-01-01',
    @MaxDate DATE = '2020-02-01';

WITH Dates(day) AS 
(
    SELECT CAST(@MinDate as Date) as day
    UNION ALL
    SELECT CAST(DATEADD(day, 1, day) as Date) as day
    FROM Dates
    WHERE CAST(DATEADD(day, 1, day) as Date) < @MaxDate
)
SELECT* FROM dates;

- cacheoff

Answer 8

如果您使用的是SQL Server 2022+或Azure SQL数据库，并且没有适用的日历表可用（或者正在使用此方法创建一个！），那么目前最简单的方法可能是使用GENERATE_SERIES。

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

SELECT DATEADD(DAY, value, @MinDate) AS [Date]
FROM GENERATE_SERIES(0, DATEDIFF(DAY ,@MinDate, @MaxDate))

- Martin Smith

Answer 9

你可以使用子查询生成一个从'2020-01-01'到'2020-12-31'的日期范围，方法是创建一个日期范围的子查询，如下所示：

SELECT DATE('2020-01-01') + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date_range
FROM (
    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS a
CROSS JOIN (
    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS b
CROSS JOIN (
    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS c
WHERE DATE('2020-01-01') + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY <= '2020-12-31';

输出：

2020-01-01
2020-01-02
2020-01-03
2020-01-04
.
.
.
2020-12-29
2020-12-30
2020-12-31

- Mohammad reza Saberi

---

如果我只想获取每个月的最后一天而不是所有日期呢？ - undefined

Answer 10

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

- Shailesh Jaiswal