Getting Dates between a range of dates

Here you go:

DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom='20000101';
SET @DateTo='20081231';
-------------------------------
WITH T(date)
AS
( 
SELECT @DateFrom 
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);

If you have the dates in a table and simply want to select those between two dates you can use

select * from yourTable where yourDate between date1 and date2

If you want to produce the dates from nothing you could do it with a loop or you could populate a temporary table with dates and then select from that.

DECLARE @Date1 DATE='2016-12-21', @Date2 DATE='2016-12-25'
SELECT DATEADD(DAY,number,@Date1) [Date] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number,@Date1) <= @Date2

Here's Oracle version of date generation:

SELECT TO_DATE ('01-OCT-2008') + ROWNUM - 1 g_date
  FROM all_objects
 WHERE ROWNUM <= 15

instead of all_objects it can be any table with enough rows to cover the required range.