首頁>Program>source

如何获取两个日期之間的日期?

我有一个可變的 @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

編譯錯誤

Incorrect syntax near the keyword 'SET'.

如何解決這个問题。

預先感谢

最新回復
  • 5月前
    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;
    

    有關此內容的更多資訊,請參见:

      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

    關於隨後在遊標中使用此日期序列,我真的建議您找到另一種方法.通常会有一个基於集合的替代方法,其效能会更好。

    關於您的資料:

     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;
    

  • 5月前
    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
    

  • 5月前
    3 #

    轻松建立表值函式,该函式將返迴包含所有日期的表。 Input dates as string 您可以使用字元串格式(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
    

  • 5月前
    4 #

    与我的情况一樣,這可能有點棘手,我無法使用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
    

  • 5月前
    5 #

    您可以尝試以下操作:

       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
    ...
    

  • java:InflateException:二进製XML檔案行#1:因OutOfMemoryError匯致類<unknown>膨脹錯誤
  • 是否可以在C#中返迴對變數的引用?