SQL to return dates that fall in period and range

Question

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

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