SQL to return dates that fall in period and range

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 a开发者_Go百科s 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

---

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'.

---

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'