Get All Days between selected duration using Sql

--Find all Days(Dates) between duration

Create FUNCTION [dbo].[BVT_GetAllDays]

(

      @DATEFROM DATETIME,

      @DATETO DATETIME

)

RETURNS

@DAYS TABLE(CDate Datetime)

AS

BEGIN

      Declare @tDays int, @counter int

      set @tDays=DATEDIFF(D,@DATEFROM,@DATETO)

      Set @counter =0

            insert into @DAYS values (@DATEFROM)

            while (@counter!=@tDays)

            begin

            Set @counter=@counter+1

            insert into @DAYS values (DATEADD (DAY,@counter,@DATEFROM))

      end

      RETURN

END

--Call Function

Select CDate from dbo.BVT_GetAllDays('01/01/2013','01/17/2013')

 

OUTPUT

 

2013-01-01 00:00:00.000

2013-01-02 00:00:00.000

2013-01-03 00:00:00.000

2013-01-04 00:00:00.000

2013-01-05 00:00:00.000

2013-01-06 00:00:00.000

2013-01-07 00:00:00.000

2013-01-08 00:00:00.000

2013-01-09 00:00:00.000

2013-01-10 00:00:00.000

2013-01-11 00:00:00.000

2013-01-12 00:00:00.000

2013-01-13 00:00:00.000

2013-01-14 00:00:00.000

2013-01-15 00:00:00.000

2013-01-16 00:00:00.000

2013-01-17 00:00:00.000