Get all dates between date range

There are various ways through which we can get all date between date range.


Method 1



WITH myCounter AS
(
  SELECT CAST('2012-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    myCounter   
  WHERE   DateValue + 1 < '2012-12-31'
)
SELECT  DateValue
FROM    myCounter
OPTION (MAXRECURSION 0)

Method 2

DECLARE @FromDate datetime

DECLARE @ToDate datetime

DECLARE @NoOfDays  INT

SELECT @FromDate = getDate() - 15

SELECT @ToDate = getDate()

DECLARE @index INT

SELECT @index = 0

SELECT @NoOfDays = datediff(day, @FromDate, @ToDate)

CREATE TABLE #tempTable

(

   ID INT NOT NULL IDENTITY(1,1)

   ,CommonDate DATETIME NULL

)

WHILE @index < @NoOfDays

   BEGIN

      INSERT INTO #tempTable (CommonDate) VALUES  (DATEADD(Day, @index, @FromDate))   

      SELECT @index = @index + 1

   END

SELECT CONVERT(VARCHAR(10), CommonDate, 102) as [Date Between] FROM #tempTable

DROP TABLE #tempTable

Method3

CREATE FUNCTION dbo.ShowDates

(

     @FromDate    char(10)  

    ,@ToDate      char(10)

)

RETURNS

@DateTable table

(

    Date datetime

)

AS

BEGIN

IF ISDATE(@FromDate)!=1 OR ISDATE(@ToDate)!=1

BEGIN

    RETURN

END

INSERT INTO @DateTable

        (Date)

    SELECT

        CONVERT(datetime,@FromDate)+n.Number-1

        FROM Numbers  n

        WHERE Number<=DATEDIFF(day,@FromDate,CONVERT(datetime,@ToDate)+1)

RETURN

END