65.9K
CodeProject is changing. Read more.
Home

Find All the Days Between Two Dates

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.64/5 (5 votes)

Aug 19, 2013

CPOL
viewsIcon

46353

Find all the days between two dates using SQL Server

Introduction

Sometimes, a business requires all days with date between two dates. There are many ways to get this done, but I am introducing a very simple way to do this using SQL Server.

Using the Code

Create PROCEDURE getAllDaysBetweenTwoDate
(
@FromDate DATETIME,    
@ToDate DATETIME
)
AS
BEGIN
    
    DECLARE @TOTALCount INT
    SET @FromDate = DATEADD(DAY,-1,@FromDate)
    Select  @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);

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

--Exec getAllDaysBetweenTwoDate '12-31-2013','01-05-2014' 
--Date Formate will be 'MM-dd-yyyy'

639460/Untitled.jpg