CODEWITHSUNDEEP
mysqldate
devmyb
devmyb

Reputation: 375

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

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

Upvotes: 0

Views: 64

Answers (1)

D Ie
D Ie

Reputation: 841

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.

Upvotes: 1

Related Questions