Get a list of dates between two dates

Question

Using standard mysql functions is there a way to write a query that will return a list of days between two dates.

eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).

Answer 1

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data. Since there were no entries for those dates, the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date.

We have a job that runs every month to ensure that the table is populated 5 years out into the future. The table is created thus:

create table all_dates (
    dt date primary key
);

No doubt there are magical tricky ways to do this with different DBMS' but we always opt for the simplest solution. The storage requirements for the table are minimal and it makes the queries so much simpler and portable. This sort of solution is almost always better from a performance point-of-view since it doesn't require per-row calculations on the data.

The other option (and we've used this before) is to ensure there's an entry in the table for every date. We swept the table periodically and added zero entries for dates and/or times that didn't exist. This may not be an option in your case, it depends on the data stored.

If you really think it's a hassle to keep the all_dates table populated, a stored procedure is the way to go which will return a dataset containing those dates. This will almost certainly be slower since you have to calculate the range every time it's called rather than just pulling pre-calculated data from a table.

But, to be honest, you could populate the table out for 1000 years without any serious data storage problems - 365,000 16-byte (for example) dates plus an index duplicating the date plus 20% overhead for safety, I'd roughly estimate at about 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), a minuscule table in the scheme of things.

Answer 2

We used this in our HRMS System you will find it useful

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

Answer 3

I would use something similar to this:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

Then the @HOLDER Variable table holds all the dates incremented by day between those two dates, ready to join at your hearts content.

Answer 4

Well how to find dates between two given date in SQL server is explain on http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html

Answer 5

For Access (or any SQL language)

1. Create one table that has 2 fields, we'll call this table tempRunDates:
   --Fields fromDate and toDate
   --Then insert only 1 record, that has the start date and the end date.

2. Create another table: Time_Day_Ref
   --Import a list of dates (make list in excel is easy) into this table.
   --The field name in my case is Greg_Dt, for Gregorian Date
   --I made my list from jan 1 2009 through jan 1 2020.

3. Run the query:

   SELECT Time_Day_Ref.GREG_DT
   FROM tempRunDates, Time_Day_Ref
   WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;
   

Easy!

Answer 6

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

I've seen variations with table-valued functions, etc.

You can also keep a permanent list of dates. We have that in our data warehouse as well as a list of times of day.