CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Nirmal R
Nirmal R

Reputation: 45

Get all dates in a week

When I select a date from dual. I have to display all the dates in particular week with days.

First I get week number using

select to_char(sysdate,'iw') FROM dual

By using week number I want to get all the dates of that particular week. I used this.

SELECT (TRUNC ( TO_DATE (SUBSTR ('201704', 1, 4) 
         || '0131', 'YYYY'|| 'MMDD'), 'IYYY') +
              ( 7 * ( TO_NUMBER (SUBSTR ('201704', 5)) - 1)) ) AS iw
FROM   DUAL

my input is sysdate.

I Have to get o/p as below.

23-Jan-2017
24-Jan-2017
25-Jan-2017
26-Jan-2017
27-Jan-2017
28-Jan-2017
29-Jan-2017

Upvotes: 2

Views: 1536

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

select trunc(sysdate,'IW') + level - 1
from dual
connect by level <= 7;

If you want to manually provide the date instead of sysdate, you can e.g. do this:

select trunc(to_date('10-Feb-2017','dd-Mon-yyyy'),'IW') + level - 1
from dual
connect by level <= 7;

If you want to apply this on more than one date, use cross join:

with t(dt) as (
  select to_date('23-Jan-2017','dd-Mon-yyyy') from dual union all
  select to_date('01-Mar-2017','dd-Mon-yyyy') from dual
)
-- test data ends here

select dt, trunc(dt,'IW') + n dt2
from t cross join (
      select level - 1 n
      from dual
      connect by level <= 7
);

Upvotes: 1

Related Questions