SQL to return dates that fall in period and range

Question

Hey stackers,

I’ve been grinding my head on this for a while… My goal is to return all dates that fall between a start and end date and have a certain period as a factor, from the start date. (hard to explain)

For example…

Start Date: Nov 20, 1987; End Date: Jan 01, 1988; Period: 10 days;

I want these dates: Nov 20, 1987; Nov 30, 1987; Dec 10, 1987; Dec 20, 1987; Dec 30, 1987;

I already have a date table with all dates from 1900 to 2099. The period could be by days, months or years. Any ideas? Let me know if you need more info.

MySQL

Answer 1

If you are using MS SQL Server 2005 or later, you can use recursive common table expressions:

WITH Days AS
(
    SELECT CONVERT(DATETIME, '11/20/87') AS Date
    UNION ALL
    SELECT DATEADD(DAY, 10, Days.Date) AS Date
    FROM Days
    WHERE Days.Date < '1/1/88'
)
SELECT Days.Date
FROM Days
WHERE Days.Date < '1/1/88'

Answer 2

For days use DATEDIFF and the modulo operation:

SELECT * FROM dates
WHERE `date` BETWEEN '1987-10-20' AND '1988-1-1'
AND DATEDIFF(`date`, '1987-10-20') % 10 = 0

For a period of 10 years, calculate the difference in the year modulo the period, and ensure that the month and day are the same:

SELECT * FROM dates
WHERE `date` BETWEEN '1980-10-20' AND '2000-10-20'
AND MONTH(date) = 10 AND DAY(date) = 20 AND (YEAR(date) - 1980) % 10 = 0

A period measured in months is not well-defined because months have different lengths. What is one month later than January 30th? You can get it working for some special cases such as 'first in the month'.

Answer 3

I've used MySQL variables to generate this sort of sequence in the past. This one caters for your example:

SET @date     = '1987-11-20';
SET @end_date = '1988-01-01';
SET @interval = 10;

SELECT `date`
FROM
    (
    SELECT
        @date AS `date`,
        @date := @date + INTERVAL @interval DAY
    FROM
        dates
    WHERE
        @date < @end_date
) AS x;

My dates table contains calendar dates - similar to the one you have. But it could contain anything really, as long as there are enough rows to cover the whole sequence.

You could generalise this for other periods, something like:

SET @date     = '1987-11-20';
SET @end_date = '1989-01-01';
SET @interval = 2;
SET @unit     = 'MONTH';

SELECT `date`
FROM
    (
    SELECT
        @date AS `date`,
        @date :=
        CASE
            WHEN @unit = 'DAY'   THEN @date + INTERVAL @interval DAY
            WHEN @unit = 'MONTH' THEN @date + INTERVAL @interval MONTH
            WHEN @unit = 'YEAR'  THEN @date + INTERVAL @interval YEAR
        END
    FROM
        dates
    WHERE
        @date < @end_date
) AS x;