CODEWITHSUNDEEP
sqloracle-database
Kevin
Kevin

Reputation: 207

How to show rows where date is equal to every 7 days from a specific day

I'd like to show every row where date_added equals '2015-02-18' and every seven days after, so '2015-02-25' and '2015-03-04' etc..

here's what I have so far

select * from table 
    where ((to_char(date_added, 'j')) / 
    ((select to_char(d,'j') from (select date '2015-02-18' d from dual)))) = 1

That gets me the first desired date, however I'm stuck as to how to express it to show the next 7 days as a step additive function.

Any help is appreciated. Thank you.

Upvotes: 1

Views: 2170

Answers (2)

PlanItMichael
PlanItMichael

Reputation: 334

I like Gordon's "mod()" solution but he's missing part of the requested solution.

In this case, I have a "calendar" table that includes a series of dates:

http://www.perpendulum.com/2012/06/calendar-table-script-for-oracle/

select *
  from calendar
  where date_time_start >= to_date('01-Jan-2013') 
    and mod(trunc(date_time_start) - to_date('01-Jan-2013'), 7) = 0;

Per the original question, you want records where the dates are equal to a given date and every seven days thereafter.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

One way to do this is with mod():

select *
from table
where mod(date_added - date '2015-02-18', 7) = 0;

Note: this assumes that the dates have no time component. If they do, then use trunc() to get rid of it.

Upvotes: 2

Related Questions