Here is another variation using views:

CREATE VIEW digits AS SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9; CREATE VIEW numbers AS SELECT ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number FROM digits as ones, digits as tens, digits as hundreds, digits as thousands; CREATE VIEW dates AS SELECT SUBDATE(CURRENT_DATE(), number) AS date FROM numbers; 

And then you can simply do (see how elegant it is?):

SELECT date FROM dates WHERE date BETWEEN '2010-01-20' AND '2010-01-24' ORDER BY date 

Update

It is worth noting that you will only be able to generate past dates starting from the current date. If you want to generate any kind of dates range (past, future, and in between), you will have to use this view instead:

CREATE VIEW dates AS SELECT SUBDATE(CURRENT_DATE(), number) AS date FROM numbers UNION ALL SELECT ADDDATE(CURRENT_DATE(), number + 1) AS date FROM numbers; 

Accepted answer didn't work for PostgreSQL (syntax error at or near "a").

The way you do this in PostgreSQL is by using generate_series function, i.e.:

SELECT day::date FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day; day ------------ 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24 (5 rows) 

Using a recursive Common Table Expression (CTE), you can generate a list of dates, then select from it. Obviously you normally wouldn't want to create three million dates, so this just illustrates the possibilities. You could simply limit the date range inside the CTE and omit the where clause from the select statement using the CTE.

with [dates] as ( select convert(datetime, '1753-01-01') as [date] --start union all select dateadd(day, 1, [date]) from [dates] where [date] < '9999-12-31' --end ) select [date] from [dates] where [date] between '2013-01-01' and '2013-12-31' option (maxrecursion 0) 

On Microsoft SQL Server 2005, generating the CTE list of all possible dates took 1:08. Generating one hundred years took less than a second.

MSSQL Query

select datetable.Date from ( select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) datetable where datetable.Date between '2014-01-20' and '2014-01-24' order by datetable.Date DESC 

Output

Date ----- 2014-01-23 12:35:25.250 2014-01-22 12:35:25.250 2014-01-21 12:35:25.250 2014-01-20 12:35:25.250 

The old school solution for doing this without a loop/cursor is to create a NUMBERS table, which has a single Integer column with values starting at 1.

CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

You need to populate the table with enough records to cover your needs:

INSERT INTO NUMBERS (id) VALUES (NULL); 

Once you have the NUMBERS table, you can use:

SELECT x.start_date + INTERVAL n.id-1 DAY FROM NUMBERS n JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date FROM DUAL) x WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24' 

The absolute low-tech solution would be:

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') FROM DUAL UNION ALL SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d') FROM DUAL UNION ALL SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d') FROM DUAL UNION ALL SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d') FROM DUAL UNION ALL SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d') FROM DUAL 

What would you use it for?

To generate lists of dates or numbers in order to LEFT JOIN on to. You would to this in order to see where there are gaps in the data, because you are LEFT JOINing onto a list of sequencial data - null values will make it obvious where gaps exist.

For Access 2010 - multiple steps required; I followed the same pattern as posted above, but thought I could help someone in Access. Worked great for me, I didn't have to keep a seeded table of dates.

Create a table called DUAL (similar to how the Oracle DUAL table works)

• ID (AutoNumber)
• DummyColumn (Text)
• Add one row values (1,"DummyRow")

Create a query named "ZeroThru9Q"; manually enter the following syntax:

SELECT 0 AS a FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual UNION ALL SELECT 4 FROM dual UNION ALL SELECT 5 FROM dual UNION ALL SELECT 6 FROM dual UNION ALL SELECT 7 FROM dual UNION ALL SELECT 8 FROM dual UNION ALL SELECT 9 FROM dual; 

Create a query named "TodayMinus1KQ" (for dates before today); manually enter the following syntax:

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate FROM (SELECT * FROM ZeroThru9Q) AS a, (SELECT * FROM ZeroThru9Q) AS b, (SELECT * FROM ZeroThru9Q) AS c 

Create a query named "TodayPlus1KQ" (for dates after today); manually enter the following syntax:

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate FROM (SELECT * FROM ZeroThru9Q) AS a, (SELECT * FROM ZeroThru9Q) AS b, (SELECT * FROM ZeroThru9Q) AS c; 

Create a union query named "TodayPlusMinus1KQ" (for dates +/- 1000 days):

SELECT MyDate FROM TodayMinus1KQ UNION SELECT MyDate FROM TodayPlus1KQ; 

Now you can use the query:

SELECT MyDate FROM TodayPlusMinus1KQ WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014# 

Elegant solution using new recursive (Common Table Expressions) functionality in MariaDB >= 10.3 and MySQL >= 8.0.

WITH RECURSIVE t as ( select '2019-01-01' as dt UNION SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30' ) select * FROM t; 

The above returns a table of dates between '2019-01-01' and '2019-04-30'. It is also decently fast. Returning 1000 years worth of dates (~365,000 days) takes about 400ms on my machine.

Procedure + temporary table:

DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE) BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE); WHILE dateStart <= dateEnd DO INSERT INTO date_range VALUES (dateStart); SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY); END WHILE; SELECT * FROM date_range; DROP TEMPORARY TABLE IF EXISTS date_range; END 

thx Pentium10 - you made me join stackoverflow :) - this is my porting to msaccess - think it'll work on any version:

SELECT date_value FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value, dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value FROM (select * from ( select top 1 "0" as espr1 from MSysObjects union all select top 1 "1" as espr2 from MSysObjects union all select top 1 "2" as espr3 from MSysObjects union all select top 1 "3" as espr4 from MSysObjects union all select top 1 "4" as espr5 from MSysObjects union all select top 1 "5" as espr6 from MSysObjects union all select top 1 "6" as espr7 from MSysObjects union all select top 1 "7" as espr8 from MSysObjects union all select top 1 "8" as espr9 from MSysObjects union all select top 1 "9" as espr9 from MSysObjects ) as a, ( select top 1 "0" as espr1 from MSysObjects union all select top 1 "1" as espr2 from MSysObjects union all select top 1 "2" as espr3 from MSysObjects union all select top 1 "3" as espr4 from MSysObjects union all select top 1 "4" as espr5 from MSysObjects union all select top 1 "5" as espr6 from MSysObjects union all select top 1 "6" as espr7 from MSysObjects union all select top 1 "7" as espr8 from MSysObjects union all select top 1 "8" as espr9 from MSysObjects union all select top 1 "9" as espr9 from MSysObjects ) as b, ( select top 1 "0" as espr1 from MSysObjects union all select top 1 "1" as espr2 from MSysObjects union all select top 1 "2" as espr3 from MSysObjects union all select top 1 "3" as espr4 from MSysObjects union all select top 1 "4" as espr5 from MSysObjects union all select top 1 "5" as espr6 from MSysObjects union all select top 1 "6" as espr7 from MSysObjects union all select top 1 "7" as espr8 from MSysObjects union all select top 1 "8" as espr9 from MSysObjects union all select top 1 "9" as espr9 from MSysObjects ) as c ) as d) WHERE date_value between dateserial([start_year], [start_month], [start_day]) and dateserial([end_year], [end_month], [end_day]); 

referenced MSysObjects just 'cause access need a table countin' at least 1 record, in a from clause - any table with at least 1 record would do.

As stated (or at least alluded to) in many of the wonderful answers already given, this problem is easily solved once you have a set of numbers to work with.

Note: The following is T-SQL but it's simply my particular implementation of general concepts already mentioned here and on the internet at large. It should be relatively simple to convert the code to your dialect of choice.

How? Consider this query:

SELECT DATEADD(d, N, '0001-01-22') FROM Numbers -- A table containing the numbers 0 through N WHERE N <= 5; 

The above produces the date range 1/22/0001 - 1/27/0001 and is extremely trivial. There are 2 key pieces of information in the above query: the start date of 0001-01-22 and the offset of 5. If we combine these two pieces of information then we obviously have our end date. Thus, given two dates, generating a range can be broken down like so:

• Find the difference between two given dates (the offset), easy:

  -- Returns 125 SELECT ABS(DATEDIFF(d, '2014-08-22', '2014-12-25'))

  Using ABS() here ensures that the date order is irrelevant.

• Generate a limited set of numbers, also easy:

  -- Returns the numbers 0-2 SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM(SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A')

  Notice we don't actually care what we're selecting FROM here. We just need a set to work with so that we count the number of rows in it. I personally use a TVF, some use a CTE, others use a numbers table instead, you get the idea. I advocate for using the most performant solution that you also understand.

Combining these two methods will solve our problem:

DECLARE @date1 DATE = '9001-11-21'; DECLARE @date2 DATE = '9001-11-23'; SELECT D = DATEADD(d, N, @date1) FROM ( SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM (SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') S ) Numbers WHERE N <= ABS(DATEDIFF(d, @date1, @date2)); 

The above example is horrible code but demonstrates how everything comes together.

More Fun

I need to do this kind of thing a lot so I encapsulated the logic into two TVFs. The first generates a range of numbers and the second uses this functionality to generate a range of dates. The math is to ensure that input order doesn't matter and because I wanted to use the full range of numbers available in GenerateRangeSmallInt.

The following function takes ~16ms of CPU time to return the maximum range of 65536 dates.

CREATE FUNCTION dbo.GenerateRangeDate ( @date1 DATE, @date2 DATE ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT D = DATEADD(d, N + 32768, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END) FROM dbo.GenerateRangeSmallInt(-32768, ABS(DATEDIFF(d, @date1, @date2)) - 32768) ); GO CREATE FUNCTION dbo.GenerateRangeSmallInt ( @num1 SMALLINT = -32768 , @num2 SMALLINT = 32767 ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( WITH Numbers(N) AS ( SELECT N FROM(VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256 ) V (N) ) SELECT TOP(ABS(CAST(@num1 AS INT) - CAST(@num2 AS INT)) + 1) N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1 FROM Numbers A , Numbers B ); 

try this.

SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day from DUAL connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ; 

For anyone who wants this as a saved view (MySQL doesn't support nested select statements in views):

create view zero_to_nine as select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9; create view date_range as select curdate() - INTERVAL (a.n + (10 * b.n) + (100 * c.n)) DAY as date from zero_to_nine as a cross join zero_to_nine as b cross join zero_to_nine as c; 

You can then do

select * from date_range 

to get

date --- 2017-06-06 2017-06-05 2017-06-04 2017-06-03 2017-06-02 ... 

You'd like to get the a date range.

In your example you'd like to get the dates between '2010-01-20' and '2010-01-24'

possible solution:

 select date_add('2010-01-20', interval row day) from ( SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=-1) r ) sequence where date_add('2010-01-20', interval row day) <= '2010-01-24' 

Explanation

MySQL has a date_add function so

select date_add('2010-01-20', interval 1 day) 

will give you

2010-01-21 

The datediff function would let you know often you'd have to repeat this

select datediff('2010-01-24', '2010-01-20') 

which returns

 4 

Getting a list of dates in a date range boils down to creating a sequence of integer numbers see generate an integer sequence in MySQL

The most upvoted answer here has taken a similar approach as https://stackoverflow.com/a/2652051/1497139 as a basis:

SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=0) r limit 4 

which will result in

row 1.0 2.0 3.0 4.0 

The rows can now be used to create a list of dates from the given start date. To include the start date we start with row -1;

select date_add('2010-01-20', interval row day) from ( SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, (SELECT @row:=-1) r ) sequence where date_add('2010-01-20', interval row day) <= '2010-01-24' 

A more generic answer that works in AWS MySQL.

select datetable.Date from ( select date_format(adddate(now(),-(a.a + (10 * b.a) + (100 * c.a))),'%Y-%m-%d') AS Date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) datetable where datetable.Date between now() - INTERVAL 14 Day and Now() order by datetable.Date DESC 

if you will ever need more then a couple days, you need a table.

Create a date range in mysql

then,

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y; 

Generate dates between two date fields

If you are aware with SQL CTE query, then this solution will helps you to solve your question

Here is example

We have dates in one table

Table Name: “testdate”

STARTDATE ENDDATE 10/24/2012 10/24/2012 10/27/2012 10/29/2012 10/30/2012 10/30/2012 

Require Result:

STARTDATE 10/24/2012 10/27/2012 10/28/2012 10/29/2012 10/30/2012 

Solution:

WITH CTE AS (SELECT DISTINCT convert(varchar(10),StartTime, 101) AS StartTime, datediff(dd,StartTime, endTime) AS diff FROM dbo.testdate UNION ALL SELECT StartTime, diff - 1 AS diff FROM CTE WHERE diff<> 0) SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime FROM CTE 

Explanation: CTE Recursive query explanation

• First part of query:
  
  SELECT DISTINCT convert(varchar(10), StartTime, 101) AS StartTime, datediff(dd, StartTime, endTime) AS diff FROM dbo.testdate
  
  Explanation: firstcolumn is “startdate”, second column is difference of start and end date in days and it will be consider as “diff” column

• Second part of query:
  
  UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0
  
  Explanation: Union all will inherit result of above query until result goes null, So “StartTime” result is inherit from generated CTE query, and from diff, decrease - 1, so its looks like 3, 2, and 1 until 0

For example

STARTDATE DIFF 10/24/2012 0 10/27/2012 0 10/27/2012 1 10/27/2012 2 10/30/2012 0 

Result Specification

STARTDATE Specification 10/24/2012 --> From Record 1 10/27/2012 --> From Record 2 10/27/2012 --> From Record 2 10/27/2012 --> From Record 2 10/30/2012 --> From Record 3 

• 3rd Part of Query
  
  

  SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime FROM CTE
  
  It will add day “diff” in “startdate” so result should be as below

Result

STARTDATE 10/24/2012 10/27/2012 10/28/2012 10/29/2012 10/30/2012 

Shorter than accepted answer, same idea:

(SELECT TRIM('2016-01-05' + INTERVAL a + b DAY) date FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d, (SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m WHERE '2016-01-05' + INTERVAL a + b DAY <= '2016-01-21') 

One more solution for mysql 8.0.1 and mariadb 10.2.2 using recursive common table expressions:

with recursive dates as ( select '2010-01-20' as date union all select date + interval 1 day from dates where date < '2010-01-24' ) select * from dates; 

It's a good idea with generating these dates on the fly. However, I do not feel myself comfortable to do this with quite large range so I've ended up with the following solution:

1. Created a table "DatesNumbers" that will hold numbers used for dates calculation:

CREATE TABLE DatesNumbers ( i MEDIUMINT NOT NULL, PRIMARY KEY (i) ) COMMENT='Used by Dates view' ; 

2. Populated the table using above techniques with numbers from -59999 to 40000. This range will give me dates from 59999 days (~164 years) behind to 40000 days (109 years) ahead:

INSERT INTO DatesNumbers SELECT a.i + (10 * b.i) + (100 * c.i) + (1000 * d.i) + (10000 * e.i) - 59999 AS i FROM (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a, (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b, (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c, (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d, (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS e ; 

3. Created a view "Dates":

SELECT i, CURRENT_DATE() + INTERVAL i DAY AS Date FROM DatesNumbers 

That's it.

• (+) Easy to read queries
• (+) No on the fly numbers generations
• (+) Gives dates in the past and in the future and there is NO UNION in view for this as in this post.
• (+) "In the past only" or "in the future only" dates could be filtered using WHERE i < 0 or WHERE i > 0 (PK)
• (-) 'temporary' table & view is used

Alright.. Try this: http://www.devshed.com/c/a/MySQL/Delving-Deeper-into-MySQL-50/
http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html
http://www.roseindia.net/sql/mysql-example/mysql-loop.shtml

Use that to, say, generate a temp table, and then do a select * on the temp table. Or output the results one at a time.
What you say you want to do can't be done with a SELECT statement, but it might be doable with things specific to MySQL.
Then again, maybe you need cursors: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

For Oracle, my solution is:

select trunc(sysdate-dayincrement, 'DD') from dual, (select level as dayincrement from dual connect by level <= 30) 

Sysdate can be changed to specific date and level number can be changed to give more dates.

if you want the list of dates between two dates:

create table #dates ([date] smalldatetime) while @since < @to begin insert into #dates(dateadd(day,1,@since)) set @since = dateadd(day,1,@since) end select [date] from #dates 

*fiddle here: http://sqlfiddle.com/#!6/9eecb/3469

set language 'SPANISH' DECLARE @table table(fechaDesde datetime , fechaHasta datetime ) INSERT @table VALUES('20151231' , '20161231'); WITH x AS ( SELECT DATEADD( m , 1 ,fechaDesde ) as fecha FROM @table UNION ALL SELECT DATEADD( m , 1 ,fecha ) FROM @table t INNER JOIN x ON DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta ) SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id ,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio ,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio ,DATEPART ( mm , fecha ) Mes_Id ,DATEPART ( yy , fecha ) Anio ,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin ,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia ,datename(MONTH, fecha) mes ,'Q' + convert(varchar(10), DATEPART(QUARTER, fecha)) Trimestre_Name FROM x OPTION(MAXRECURSION 0) 

DELIMITER $$ CREATE PROCEDURE GenerateRangeDates(IN dateStart DATE, IN dateEnd DATE) BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE); loopDate: LOOP INSERT INTO dates(day) VALUES (dateStart); SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY); IF dateStart <= dateEnd THEN ITERATE loopDate; ELSE LEAVE loopDate; END IF; END LOOP loopDate; SELECT day FROM dates; DROP TEMPORARY TABLE IF EXISTS dates; END $$ -- Call procedure call GenerateRangeDates( now() - INTERVAL 40 DAY, now() ); 

SQLite version of RedFilters top solution

select d.Date from ( select date(julianday('2010-01-20') + (a.a + (10 * b.a) + (100 * c.a))) as Date from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) d where d.Date between '2010-01-20' and '2010-01-24' order by d.Date 

improved with weekday an joining a custom holiday table microsoft MSSQL 2012 for powerpivot date table https://gist.github.com/josy1024/cb1487d66d9e0ccbd420bc4a23b6e90e

with [dates] as ( select convert(datetime, '2016-01-01') as [date] --start union all select dateadd(day, 1, [date]) from [dates] where [date] < '2018-01-01' --end ) select [date] , DATEPART (dw,[date]) as Wochentag , (select holidayname from holidaytable where holidaytable.hdate = [date]) as Feiertag from [dates] where [date] between '2016-01-01' and '2016-31-12' option (maxrecursion 0) 

WITH Digits AS (SELECT 0 D UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9), Dates AS (SELECT adddate('1970-01-01',t4.d*10000 + t3.d*1000 + t2.d*100 + t1.d*10 +t0.d) AS date FROM Digits AS t0, Digits AS t1, Digits AS t2, Digits AS t3, Digits AS t4) SELECT * FROM Dates WHERE date BETWEEN '2017-01-01' AND '2017-12-31' 

Can create a procedure also to create calendar table with timestmap different from day. If you want a table for each quarter

e.g.

2019-01-22 08:45:00 2019-01-22 09:00:00 2019-01-22 09:15:00 2019-01-22 09:30:00 2019-01-22 09:45:00 2019-01-22 10:00:00 

you can use

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_calendar_table`() BEGIN select unix_timestamp('2014-01-01 00:00:00') into @startts; select unix_timestamp('2025-01-01 00:00:00') into @endts; if ( @startts < @endts ) then DROP TEMPORARY TABLE IF EXISTS calendar_table_tmp; CREATE TEMPORARY TABLE calendar_table_tmp (ts int, dt datetime); WHILE ( @startts < @endts) DO SET @startts = @startts + 900; INSERT calendar_table_tmp VALUES (@startts, from_unixtime(@startts)); END WHILE; END if; END 

and then manipulate through

select ts, dt from calendar_table_tmp; 

that give you also ts

'1548143100', '2019-01-22 08:45:00' '1548144000', '2019-01-22 09:00:00' '1548144900', '2019-01-22 09:15:00' '1548145800', '2019-01-22 09:30:00' '1548146700', '2019-01-22 09:45:00' '1548147600', '2019-01-22 10:00:00' 

from here you can start to add other information such as

select ts, dt, weekday(dt) as wd from calendar_table_tmp; 

or create a real table with create table statement

Similar to D'Arcy Rittich's Answer, but for SQL SERVER

;WITH t AS ( SELECT n = a.n * 10 + b.n * 100 + c.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n) CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n) ) SELECT DATEADD(DAY, t.n, '2022-01-01') FROM t ORDER BY T.n; 

or without CTE

SELECT DATEADD(DAY, nums.n, '2022-01-01') AS d FROM ( SELECT n = a.n * 10 + b.n * 100 + c.n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n) CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n) ) nums order by d