Skip to Main Content
  • Questions

  • Week start and end date for a given date

Breadcrumb

Question and Answer

Sergiusz Wolicki

Thanks for the question, lingam.

Asked: March 12, 2018 - 11:07 am UTC

Last updated: March 19, 2018 - 8:12 pm UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I want the sql to get the start of the week date and end of the week date for a given date and my week starts from saturday and ends with friday.

Example,

if the given date is 03/mar/2018 then start date is - 03/mar/2018 and end date is 09/mar/2018
if the given date is 04/mar/2018 then start date is - 03/mar/2018 and end date is 09/mar/2018
if the given date is 05/mar/2018 then start date is - 03/mar/2018 and end date is 09/mar/2018

if the given date is 02/mar/2018 then start date is - 24/feb/2018 and end date is 03/mar/2018

and we said...

If you can control the NLS territory setting (or even better: you want to adapt to it), then the simplest solution is to use TRUNC(SYSDATE,'D') and TRUNC(SYSDATE,'D')+6 :

alter session set nls_territory='saudi arabia';
select today
     , trunc(today,'D') start_date
     , trunc(today,'D') + 6 end_date
  from (select date '2018-02-28'+level today from dual connect by level<=10);


The above query adapts to the week definition for a given territory: the week will start on Sunday in US and on Monday in Europe. It will start on Saturday in Arabic countries.

If you want to hardcode the week start to Saturday and make it independent of NLS settings, then you need to do some more complex date arithmetic, because TRUNC does not accept NLS_TERRITORY as a direct parameter:

select today
     , today - mod(to_number(to_char(today,'J')) - 2415026,7) start_date
     , today - mod(to_number(to_char(today,'J')) - 2415026,7) + 6  end_date
  from (select date '2018-02-28'+level today from dual connect by level<=10);


The above query uses the Julian date (2415026) of the first Saturday in the 20th century (January 6th, 1900) as an anchor point. (You could use any Saturday before your oldest "today" as the anchor.) The modulo (MOD) expression returns 0 if today is Saturday, 1 if it is Sunday, etc. This offset is subtracted from today, giving the closest Saturday before today. The end date is obviously the start date plus 6 days. By moving the anchor date slightly, you can hardcode any day as the first day of a week.

Correction to your example: if today is 02/mar/2018 then start date is 24/feb/2018 but end date is 02/mar/2018, not 03/mar/2018.

Rating

  (2 ratings)

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

Comments

next_day option

Brad, March 13, 2018 - 6:44 pm UTC

we have also used the next_day for doing windowing outside of NLS setting. 

select today
     , next_day(today-7,'SAT') as start_date
     , next_day(today,'FRI') as end_date
 from (select date '2018-02-28'+level today from dual connect by level<=10);

Sergiusz Wolicki
March 15, 2018 - 4:34 am UTC


The NEXT_DAY method is a nice one but note that it is not NLS-independent. It depends on NLS_DATE_LANGUAGE. 'SAT' and 'FRI' will not work if, let's say, NLS_DATE_LANGUAGE=POLISH. You need to use 'SO' and 'PT' in this case.

International week modification ?

lh, March 15, 2018 - 9:47 am UTC

select today
, trunc(today+2,'IW') -2 start_date
, trunc(today+2,'IW') -2 + 6 end_date
from (select date '2018-02-28'+level today from dual connect by level<=10);
Sergiusz Wolicki
March 19, 2018 - 8:12 pm UTC

This is a very good idea! I forgot this rounding format element. Thanks!
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.