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

Question

提问by user3193557

How to get the dates between two dates?

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

I have a variable @MAXDATEwhich is storing the maximum date from the table. Now I want to get the all dates between @Maxdateand 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

提前致谢

Answer 1

回答by GarethD

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:

有关这方面的进一步阅读，请参见：

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;

Answer 2

回答by Anvesh

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

Answer 3

回答by Nikos Stasinos

Easily create a Table Value Function that will return a table with all dates. Input dates as stringYou 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

Answer 4

回答by GoldBishop

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

Answer 5

回答by rchacko

This can be considered as bit tricky way as in my situation, I can't use a CTE table, so decided to join with "sys.all_objects" and then created row numbers and added that to start date till it reached the end date.

这可以被认为是有点棘手的方式，因为在我的情况下，我不能使用 CTE 表，所以决定加入“sys.all_objects”，然后创建行号并将其添加到开始日期，直到它到达结束日期。

See the code below where I generated all dates in Jul 2018. Replace hard coded dates with your own variables(tested in SQL Server 2016):

请参阅下面的代码，其中我在 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

Answer 6

回答by Chao Chen

You can try this:

你可以试试这个：

    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

The output will be something like this, you can format the date as you like:

输出将是这样的，您可以根据需要格式化日期：

Answer 7

回答by Shailesh Jaiswal

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

Answer 8

回答by Сергей Макковеев

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

我列出了 2 周后的日期。您可以使用变量 @period OR 函数 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

Answer 9

回答by WonderWorker

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.

如您所见，查询快速、准确且用途广泛。

Answer 10

回答by Hari Kumar

DECLARE @FirstDate DATE = '2018-01-01'
DECLARE @LastDate Date = '2018-12-31'
DECLARE @tbl TABLE(ID INT IDENTITY(1,1) PRIMARY KEY,CurrDate date)
INSERT @tbl VALUES( @FirstDate)
WHILE @FirstDate < @LastDate
BEGIN
SET @FirstDate = DATEADD( day,1, @FirstDate)
INSERT @tbl VALUES( @FirstDate)
END
INSERT @tbl VALUES( @LastDate) 

SELECT * FROM @tbl

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

提问by user3193557

回答by GarethD

回答by Anvesh

回答by Nikos Stasinos

回答by GoldBishop

回答by rchacko

回答by Chao Chen

回答by Shailesh Jaiswal

回答by Сергей Макковеев

回答by WonderWorker

回答by Hari Kumar

相关推荐

最近更新

标签

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

提问by user3193557

回答by GarethD

回答by Anvesh

回答by Nikos Stasinos

回答by GoldBishop

回答by rchacko

回答by Chao Chen

回答by Shailesh Jaiswal

回答by Сергей Макковеев

回答by WonderWorker

回答by Hari Kumar

相关推荐

SQL nvarchar 值“3001822585”的转换溢出了 int 列

SQL Postgres - 如何检查空数组

SQL 查找前一行和当前行之间的差异

varchar(max) MS SQL Server 2000，有问题吗？

相关推荐

最近更新

标签