Skip to Main Content
  • Questions

  • calculating this week last week & next week

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 04, 2007 - 3:43 pm UTC

Last updated: April 05, 2007 - 4:05 pm UTC

Version: 920

Viewed 1000+ times

You Asked

sir,
if for any date, i want to calculate this week (starting from monday to saturday), last week starting from last monday to last saturday and next week starting from next monday to next saturday do you think my following logic is correct?
thanks

select to_char(next_day(sysdate, 'SAT'))|| ' to ' ||to_char(next_day(sysdate, 'SAT')-6) this_week,
to_char((next_day(sysdate, 'SAT')-6)-7)|| ' to ' ||to_char(next_day(sysdate, 'SAT')-7) last_week,
to_char(next_day(sysdate, 'SAT')+7)|| ' to ' ||to_char(next_day(sysdate, 'SAT')) next_week
from dual
/
THIS_WEEK LAST_WEEK NEXT_WEEK
---------------------- ---------------------- ----------------------
07-APR-07 to 01-APR-07 25-MAR-07 to 31-MAR-07 14-APR-07 to 07-APR-07

and Tom said...

doesn't look right - SUN is a hard day to figure out for. Here is your output, versus another approach:

ops$tkyte%ORA10GR2> select the_date,
  2  to_char(next_day(the_date, 'SAT'))|| ' to ' ||to_char(next_day(the_date, 'SAT')-6) this_week,
  3  to_char((next_day(the_date, 'SAT')-6)-7)|| ' to ' ||to_char(next_day(the_date, 'SAT')-7) last_week,
  4  to_char(next_day(the_date, 'SAT')+7)|| ' to ' ||to_char(next_day(the_date, 'SAT')) next_week
  5  from (select sysdate+rownum-1 the_date from all_users where rownum <= 7)
  6  /

THE_DATE        THIS_WEEK                          LAST_WEEK                          NEXT_WEEK
--------------- ---------------------------------- ---------------------------------- ----------------------------------
Thu Apr 05 2007 Sat Apr 07 2007 to Sun Apr 01 2007 Sun Mar 25 2007 to Sat Mar 31 2007 Sat Apr 14 2007 to Sat Apr 07 2007
Fri Apr 06 2007 Sat Apr 07 2007 to Sun Apr 01 2007 Sun Mar 25 2007 to Sat Mar 31 2007 Sat Apr 14 2007 to Sat Apr 07 2007
Sat Apr 07 2007 Sat Apr 14 2007 to Sun Apr 08 2007 Sun Apr 01 2007 to Sat Apr 07 2007 Sat Apr 21 2007 to Sat Apr 14 2007
Sun Apr 08 2007 Sat Apr 14 2007 to Sun Apr 08 2007 Sun Apr 01 2007 to Sat Apr 07 2007 Sat Apr 21 2007 to Sat Apr 14 2007
Mon Apr 09 2007 Sat Apr 14 2007 to Sun Apr 08 2007 Sun Apr 01 2007 to Sat Apr 07 2007 Sat Apr 21 2007 to Sat Apr 14 2007
Tue Apr 10 2007 Sat Apr 14 2007 to Sun Apr 08 2007 Sun Apr 01 2007 to Sat Apr 07 2007 Sat Apr 21 2007 to Sat Apr 14 2007
Wed Apr 11 2007 Sat Apr 14 2007 to Sun Apr 08 2007 Sun Apr 01 2007 to Sat Apr 07 2007 Sat Apr 21 2007 to Sat Apr 14 2007

7 rows selected.

ops$tkyte%ORA10GR2> select the_date,
  2         begin_this_week-7 begin_prior_week, end_this_week-7 end_prior_week,
  3             begin_this_week, end_this_week,
  4         begin_this_week+7 begin_next_week, end_this_week+7 end_next_week
  5    from
  6  (
  7  select the_date,
  8         next_day(the_date-7,'mon') begin_this_week, next_day(the_date-2,'sat') end_this_week
  9  from (select sysdate+rownum-1 the_date from all_users )
 10  )
 11  /

THE_DATE        BEGIN_PRIOR_WEE END_PRIOR_WEEK  BEGIN_THIS_WEEK END_THIS_WEEK   BEGIN_NEXT_WEEK END_NEXT_WEEK
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
Thu Apr 05 2007 Mon Mar 26 2007 Sat Mar 31 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007
Fri Apr 06 2007 Mon Mar 26 2007 Sat Mar 31 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007
Sat Apr 07 2007 Mon Mar 26 2007 Sat Mar 31 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007
Sun Apr 08 2007 Mon Mar 26 2007 Sat Mar 31 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007
Mon Apr 09 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Tue Apr 10 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Wed Apr 11 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Thu Apr 12 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Fri Apr 13 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Sat Apr 14 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Sun Apr 15 2007 Mon Apr 02 2007 Sat Apr 07 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007
Mon Apr 16 2007 Mon Apr 09 2007 Sat Apr 14 2007 Mon Apr 16 2007 Sat Apr 21 2007 Mon Apr 23 2007 Sat Apr 28 2007

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sachin, November 25, 2008 - 6:03 am UTC

i got query to get range in a week this will be helpful .

select week_number,lag(my_date,1) over ( order by my_date ) Start_week ,my_date end_week from
(select trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') +r My_Date,
to_char(trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') + r,'YYYY') || ' Week# ' ||
to_char(trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') + r-1,'IW') Week_Number
from ( select (rownum-1)*7 r
from all_objects
)) W
Regards
Sachin
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.