How to get 7th date from past by excluding specific dates

Question

I've prepared a MySql query to get exact 7th past date from the given date by excluding list of specific dates i.e.

If I'm passing date as 2016-10-21 then output'll be 2016-10-12 by excluding specific dates from given set of comma separated dates 2016-10-16,2016-10-15,2016-10-10 for this I've tried something

SELECT *
FROM 
table where id = 46 and 
(
startdate >= DATE_SUB("2016-10-21 00:00:00",INTERVAL 7 DAY) and 
startdate <= DATE_SUB("2016-10-21 23:59:59",INTERVAL 7 DAY) and 
startdate NOT IN ('2016-10-16 00:00:00','2016-10-15 00:00:00','2016-10-10 00:00:00')
)

But it doesn't seems to work as expected. How to exclude those given comma separated dates within DATE_SUB function

Input : 2016-10-21 Expected Output : 2016-10-12

Please show us sample input and desired output. Your question is unclear. — Tim Biegeleisen
You mean, going back X days from a give starting date and bypassing dates that are found in a pre-defined list? — FDavidov
Yes @FDavidov thats what I'm looking for but right now that X = 7 — devmyb

D Ie D Ie · Accepted Answer · 2016-11-09T08:05:48

First I would substract from today's date, each of the excluding dates. If the results are <=7, I would increment 'c' counter for each.

Finally, I'd just have to add 'c' to '7', and make a simple DATE_SUB with the origin date and the result of adding 7 to the counter.

How to get 7th date from past by excluding specific dates

1 Answers