如何获取sql server中两个日期之间的所有日期

I want to get all dates between startend and Enddate.I used between to get the desired result. But the between function skipping the current date.

我想得到startend和Enddate之间的所有日期。我用它来获得所需的结果。但是函数跳过当前日期。

Declare @StartDate Datetime  ='2014-04-01 11:13:37'
               ,@EndDate datetime ='2014-04-04 11:13:37'

Query:-

Select * from table where date between @Startdate and @EndDate

Current Result:-

2014-04-02 11:13:37
2014-04-03 11:13:37
2014-04-04 11:13:37

Expected result:-

2014-04-01 11:13:37
2014-04-02 11:13:37
2014-04-03 11:13:37
2014-04-04 11:13:37

3 个解决方案

#1

You Can Try this

你可以试试这个

Declare @StartDate Date  ='2014-04-01'
               ,@EndDate date ='2014-04-04'

                  (OR)

Declare @StartDate Datetime  ='2014-04-01  00:00:00'
               ,@EndDate datetime ='2014-04-04  12:59:59'

Query:-

Select * from table where date between @Startdate and @EndDate

#2

You could create a procedure like this:

你可以创建一个这样的过程:

CREATE PROCEDURE getAllDaysBetweenTwoDate
    (
    @StartDate DATETIME,    
    @EndDate DATETIME
    )
    AS
    BEGIN

        DECLARE @TOTALCount INT
        SET @StartDate = DATEADD(DAY,-1,@StartDate)
        Select  @TOTALCount= DATEDIFF(DD,@StartDate,@EndDate);

        WITH d AS 
                (
                  SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
                    OVER (ORDER BY object_id), REPLACE(@StartDate,'-',''))
                  FROM sys.all_objects
                )
            SELECT AllDays From d

        RETURN 
    END
    GO

Courtesy: Find All the Days Between Two Dates

礼貌:查找两个日期之间的所有日子

#3

Your query works for me perfectly.

您的查询非常适合我。

DECLARE @StartDate DATETIME = '2014-04-01 11:13:37',
        @EndDate   DATETIME = '2014-04-04 11:13:37';

WITH cte_dates
AS
(
    SELECT DATEADD(MONTH,-1,@startDate) dates --get dates from a month before start date
    UNION ALL
    SELECT DATEADD(DAY,1,dates)
    FROM cte_dates
    WHERE dates < DATEADD(MONTH,1,@EndDate) --up to a month after end date
)

SELECT dates
FROM cte_dates 
WHERE dates BETWEEN @Startdate AND @EndDate

Results:

dates
-----------------------
2014-04-01 11:13:37.000
2014-04-02 11:13:37.000
2014-04-03 11:13:37.000
2014-04-04 11:13:37.000

This means that it's probably an issue with your data, specifically the time section. If you don't need to look at time and only need specific dates, try this instead:

这意味着它可能是您的数据的问题,特别是时间部分。如果您不需要查看时间并且只需要特定日期,请尝试以下方法:

DECLARE @StartDate DATE = '2014-04-01',
        @EndDate   DATE = '2014-04-04';

SELECT *
FROM yourTable
WHERE CAST([date] AS DATE) BETWEEN @startDate AND @EndDate