SQL Query which returns a table where each row represents a date in a given range

Question

Is it possible to create a SQL query which will return one column which contains the dates from a given date range (e.g. all dates from last year till today). E.g.

dat
----
2007-10-01
2007-10-02
2007-10-03
2007-10-04
...

I am wondering if this is possible as an alternative to creating a table which holds all those dates precalculated.

Updated: I need a solution for MYSQL. I am not interested in any other DBs in this case.

Answer 1

AFAIK you cannot do that with a single SQL query. However the following block of code will do the job.

Currently in Transact-SQL (for SQL Server). I do not know how this translates to MySQL.

DECLARE @start datetime
DECLARE @end datetime
DECLARE @results TABLE
(
   val datetime not null
)
set @start = '2008-10-01'
set @end = getdate()
while @start < @end
begin
    insert into @results values(@start)
    SELECT @start = DATEADD (d, 1, @start)
end
select val from @results

This outputs:

2008-10-01 00:00:00.000
2008-10-02 00:00:00.000
2008-10-03 00:00:00.000

Answer 2

smink's suggestion should translate nicely into MySQL's stored procedures. try that.

Answer 3

could someone translate this into mysql ... thx

Answer 4

Prior to CTEs, one would use a standard pre-loaded table of integer numbers (usually a few thousand in a utility table see this article) and join to it as necessary. This would work for you in mysql:

CREATE TABLE dbo.Numbers 
( 
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024 
BEGIN 
    INSERT dbo.Numbers DEFAULT VALUES 
END

SELECT DATEADD(dd, Number, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(yy, -1, GETDATE())))) AS Date
FROM Numbers
WHERE Number BETWEEN 0 AND 366

In SQL Server 2005, you can use common table expressions and recursion:

WITH DateRange(Date) AS
(
    SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(yy, -1, GETDATE()))) AS Date
    UNION ALL
    SELECT DATEADD(day, 1, Date) AS Date
    FROM DateRange
    WHERE Date <= GETDATE()
)
SELECT Date 
FROM DateRange
OPTION (MAXRECURSION 366)

Answer 5

guys i need it for mysql ... not mssql, oracle, ...

Answer 6

I do not have a MySQL instance at hand just now, but see if this will do. Substitute parameters as appropriate. I hard-coded 2007-01-01 for the example.

Regards.

SELECT
    ADDDATE('2007-01-01' INTERVAL SeqValue DAY) DateValue
FROM
(
SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS
) SEQ
WHERE
    SEQ.SeqValue < = 366 AND
    ADDDATE('2007-01-01' INTERVAL SeqValue DAY) < ADDDATE('2007-01-01' INTERVAL 1 YEAR)
ORDER BY
    ADDDATE('2007-01-01' INTERVAL SeqValue DAY) ASC