1

I want to change the start day of week in Oracle, and I need to calculate some reports for each week from daily/hourly data. Through lot of Google search I got only NLS_TERROTORY option, from which I don't know how to set to TUESDAY. Even if there is a territory, then how do change to different day in future. Thanks in advance!

Ram
  • 845
  • 1
  • 13
  • 26

4 Answers4

3

You can create a package to contain custom functions functions relating to the new start of the week:

Note: numbering of weeks of the year is based on the first week of the year containing the first starting day of a week (i.e. Tuesday - so if Jan 1st is a Wednesday then the first week of the year would start on the Jan 7th). If you want a different logic then you will need to modify the WEEK_OF_YEAR function.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE PACKAGE change_week_start IS
  week_start CONSTANT VARCHAR2(9) := 'TUESDAY';

  FUNCTION TRUNC_TO_WEEK_START(
    in_date IN DATE
  ) RETURN DATE;

  FUNCTION DAY_OF_WEEK(
    in_date IN DATE
  ) RETURN NUMBER;

  FUNCTION WEEK_OF_YEAR(
    in_date IN DATE
  ) RETURN NUMBER;

  FUNCTION TO_CHAR_WEEK(
    in_date IN DATE
  ) RETURN VARCHAR2;
END;
/

CREATE PACKAGE BODY change_week_start IS
  FUNCTION TRUNC_TO_WEEK_START(
    in_date IN DATE
  ) RETURN DATE
  IS
  BEGIN
    RETURN NEXT_DAY( TRUNC( in_date ) - 7, week_start );
  END;

  FUNCTION DAY_OF_WEEK(
    in_date IN DATE
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN ( TRUNC( in_date ) - TRUNC_TO_WEEK_START( in_date ) ) + 1;
  END;

  FUNCTION WEEK_OF_YEAR(
    in_date IN DATE
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN TRUNC(
             (
               in_date
               -
               TRUNC_TO_WEEK_START(
                 TRUNC( TRUNC_TO_WEEK_START( in_date ), 'YYYY' ) + 6
               )
             ) / 7
           ) + 1;
  END;

  FUNCTION TO_CHAR_WEEK(
    in_date IN DATE
  ) RETURN VARCHAR2
  IS
  BEGIN
    RETURN TO_CHAR( TRUNC_TO_WEEK_START( in_date ), 'FMYYYY' )
           || '-W' || TO_CHAR( WEEK_OF_YEAR( in_date ), 'FM00' )
           || '-' || DAY_OF_WEEK( in_date );
  END;
END;
/

Query 1:

SELECT value,
       CHANGE_WEEK_START.TO_CHAR_WEEK( value ) AS week,
       TO_CHAR( value, 'DAY' ) AS day
FROM   (
  SELECT TRUNC( SYSDATE, 'YYYY' ) + LEVEL - 1 AS value
  FROM   DUAL
  CONNECT BY LEVEL <= 14
)

Results:

|                VALUE |       WEEK |       DAY |
|----------------------|------------|-----------|
| 2018-01-01T00:00:00Z | 2017-W52-7 | MONDAY    |
| 2018-01-02T00:00:00Z | 2018-W01-1 | TUESDAY   |
| 2018-01-03T00:00:00Z | 2018-W01-2 | WEDNESDAY |
| 2018-01-04T00:00:00Z | 2018-W01-3 | THURSDAY  |
| 2018-01-05T00:00:00Z | 2018-W01-4 | FRIDAY    |
| 2018-01-06T00:00:00Z | 2018-W01-5 | SATURDAY  |
| 2018-01-07T00:00:00Z | 2018-W01-6 | SUNDAY    |
| 2018-01-08T00:00:00Z | 2018-W01-7 | MONDAY    |
| 2018-01-09T00:00:00Z | 2018-W02-1 | TUESDAY   |
| 2018-01-10T00:00:00Z | 2018-W02-2 | WEDNESDAY |
| 2018-01-11T00:00:00Z | 2018-W02-3 | THURSDAY  |
| 2018-01-12T00:00:00Z | 2018-W02-4 | FRIDAY    |
| 2018-01-13T00:00:00Z | 2018-W02-5 | SATURDAY  |
| 2018-01-14T00:00:00Z | 2018-W02-6 | SUNDAY    |
MT0
  • 143,790
  • 11
  • 59
  • 117
  • This is not matching during 01-JAN-16 to 04-JAN-16 period. Here 4 days falling on 2016, hence Week 01 should be calculated from last year (2015) 3 days. This case has been handled in my answer. You can cross check the calculation using outlook by changing week start date. – Ram Jan 16 '18 at 06:10
  • @Ram `2016-01-01` is a Friday and `2016-01-04` is a Monday they are all part of the week starting Tuesday `2015-12-29` so by the logic I implemented (since you did not specify any in your question) they are part of the `2016-W52` week so I cannot see anything wrong with the functions. The same thing happens with ISO weeks - the first ISO week of the year is defined as the week containing 4th January - so the preceding days of the calendar year can be in a different ISO year. – MT0 Jan 16 '18 at 08:35
  • @MTO Yes, I understand that. But, for ISO week calculation also if we notice, when 4 or more days in a week falls in the next year then remaining days of the current year considered as week 01. This you can find in Oracle week start day as Monday for the period 29-DEC-14 to 04-JAN-15. Hence, the script may require just those conditions to be added which implies previous year end week number also required during first week, which may go to week 53. – Ram Jan 16 '18 at 11:05
  • @Ram You have not specified any requirements in your question (especially not towards whether a week is in a particular year). I have given an answer based on the first week of the year being the week containing the first day of the year that is at the start of the week (i.e. the first Tuesday of the year). If you have different requirements then you can take the basis of my answer and implement whatever unspecified logic you find appropriate in your implementation. – MT0 Jan 16 '18 at 13:37
  • @MTO I accepted this as an useful answer and up-voted accordingly. But, this is not exact when comes Oracle's week calculation neither ISO standard week (IW) nor Week of year (WW). I have asked in my question 'I want to change the start day of week in Oracle'. If I make it correct answer then minor testing I have mentioned in previous comments may be missed by future surfers. That is why I am not changing to correct answer. – Ram Jan 16 '18 at 14:35
  • @Ram Updated to make it clearer what logic the week of the year uses. – MT0 Jan 16 '18 at 15:26
  • @MTO Cool then :) Hope you understood my concern! – Ram Jan 16 '18 at 15:40
2

With this PL/SQL block you get all possible days of week start:

BEGIN
    FOR aLang IN (SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'TERRITORY' ORDER BY VALUE) LOOP
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = '''||aLang.VALUE||'''';        
        DBMS_OUTPUT.PUT_LINE(aLang.VALUE || ' -> ' || TO_CHAR(TRUNC(SYSDATE, 'D'), 'Day'));
    end loop;   
end;

ALBANIA -> Monday
ALGERIA -> Saturday
AMERICA -> Sunday
ARGENTINA -> Monday
AUSTRALIA -> Monday
AUSTRIA -> Monday
AZERBAIJAN -> Monday
BAHRAIN -> Saturday
BANGLADESH -> Friday
BELARUS -> Monday
BELGIUM -> Monday
BRAZIL -> Sunday
BULGARIA -> Monday
CANADA -> Sunday
CATALONIA -> Monday
CHILE -> Monday
CHINA -> Sunday
CIS -> Monday
COLOMBIA -> Sunday
COSTA RICA -> Sunday
CROATIA -> Monday
CYPRUS -> Monday
CZECH REPUBLIC -> Monday
CZECHOSLOVAKIA -> Monday
DENMARK -> Monday
DJIBOUTI -> Saturday
ECUADOR -> Monday
EGYPT -> Saturday
EL SALVADOR -> Sunday
ESTONIA -> Monday
FINLAND -> Monday
FRANCE -> Monday
FYR MACEDONIA -> Monday
GERMANY -> Monday
GREECE -> Monday
GUATEMALA -> Sunday
HONG KONG -> Sunday
HUNGARY -> Monday
ICELAND -> Monday
INDIA -> Sunday
INDONESIA -> Monday
IRAQ -> Saturday
IRELAND -> Monday
ISRAEL -> Sunday
ITALY -> Monday
JAPAN -> Sunday
JORDAN -> Saturday
KAZAKHSTAN -> Monday
KOREA -> Sunday
KUWAIT -> Saturday
LATVIA -> Monday
LEBANON -> Saturday
LIBYA -> Saturday
LITHUANIA -> Monday
LUXEMBOURG -> Monday
MACEDONIA -> Monday
MALAYSIA -> Sunday
MAURITANIA -> Saturday
MEXICO -> Monday
MOROCCO -> Saturday
NEW ZEALAND -> Monday
NICARAGUA -> Monday
NORWAY -> Monday
OMAN -> Saturday
PANAMA -> Sunday
PERU -> Sunday
PHILIPPINES -> Sunday
POLAND -> Monday
PORTUGAL -> Sunday
PUERTO RICO -> Sunday
QATAR -> Saturday
ROMANIA -> Monday
RUSSIA -> Monday
SAUDI ARABIA -> Saturday
SERBIA AND MONTENEGRO -> Monday
SINGAPORE -> Sunday
SLOVAKIA -> Monday
SLOVENIA -> Monday
SOMALIA -> Saturday
SOUTH AFRICA -> Sunday
SPAIN -> Monday
SUDAN -> Saturday
SWEDEN -> Monday
SWITZERLAND -> Monday
SYRIA -> Saturday
TAIWAN -> Sunday
THAILAND -> Sunday
THE NETHERLANDS -> Monday
TUNISIA -> Saturday
TURKEY -> Monday
UKRAINE -> Monday
UNITED ARAB EMIRATES -> Saturday
UNITED KINGDOM -> Monday
UZBEKISTAN -> Monday
VENEZUELA -> Sunday
VIETNAM -> Sunday
YEMEN -> Saturday
YUGOSLAVIA -> Monday

You see, there is no territory where first day of week is Tuesday, so you have to build your own function. Solution from Littlefoot should work well.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
1

Us, Europeans, have Monday as the first day of the week. Therefore, if you set NLS_TERRITORY to a country in Europe (such as United Kingdom, or - why not - Croatia), you'll get "1" as the result. Here's a short demo:

SQL> ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';

Session altered.

SQL> select to_char(sysdate, 'D') european from dual;

E
-
1

SQL>
SQL> ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

Session altered.

SQL> select to_char(sysdate, 'D') american from dual;

A
-
2

SQL> ALTER SESSION SET NLS_TERRITORY = 'CROATIA';

Session altered.

SQL> select to_char(sysdate, 'D') croatian from dual;

C
-
1

SQL>

[EDIT] Custom function that returns day of the week:

SQL> create or replace function f_day_1 (par_date in date default sysdate)
  2    return varchar2
  3  is
  4  begin
  5    return case to_char(par_date, 'FmDay', 'nls_date_language = english')
  6             when 'Monday'    then 7
  7             when 'Tuesday'   then 1
  8             when 'Wednesday' then 2
  9             when 'Thursday'  then 3
 10             when 'Friday'    then 4
 11             when 'Saturday'  then 5
 12             when 'Sunday'    then 6
 13           end;
 14  end;
 15  /

Function created.

SQL> -- today
SQL> select f_day_1 from dual;

F_DAY_1
------------------------------------------------------------------------------
7

SQL> -- tomorrow is Tuesday
SQL> select f_day_1 (date '2018-01-16') from dual;

F_DAY_1(DATE'2018-01-16')
------------------------------------------------------------------------------
1

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks for your response. But, not sure you really got my question. I want to change first day of week to Tuesday (not Monday or Sunday). – Ram Jan 15 '18 at 06:36
  • I see; I edited my message, have a look. I don't know whether you can actually *set* the first day of the week to be Tuesday, but - you can write a function which will *mimic* what you need and call it instead of the usual one. – Littlefoot Jan 15 '18 at 06:58
  • I already made function to calculate week data (grouping). But, now my only problem is calculating week number. By default, oracle week number can be fetched from to_char function with IW. – Ram Jan 15 '18 at 07:05
0

Here is a solution I derived on my own with 3 Views for 2015th year and can be configured to run dynamically for any year.

--To calculate all days of one complete year.

CREATE OR REPLACE VIEW  CUSTOM_DAYS_AND_WEEKS
AS
    SELECT DAYS.*,
        CASE
            WHEN ((EXTRACT(YEAR FROM DAYS.WEEK_START)   <>EXTRACT(YEAR FROM DAYS.WEEK_END))
            AND (((TRUNC(WEEK_END,'YYYY')-1)-WEEK_START)>=3))
            THEN TO_CHAR(WEEK_START,'YYYY')
            WHEN ((EXTRACT(YEAR FROM DAYS.WEEK_START) <>EXTRACT(YEAR FROM DAYS.WEEK_END))
            AND (WEEK_START-(TRUNC(WEEK_START,'YYYY'))>=3))
            THEN TO_CHAR(WEEK_END,'YYYY')
            ELSE TO_CHAR(WEEK_START,'YYYY')
        END AS YEAR_NO
    FROM
        (SELECT "A1"."DD" "DD",
            TO_CHAR("A1"."DD",'DY') "DAY",
            DECODE(TO_CHAR("A1"."DD",'DY'),'TUE', "A1"."DD",NEXT_DAY("A1"."DD" -7, 'TUESDAY')) WEEK_START,
            DECODE(TO_CHAR("A1"."DD",'DY'),'MON',"A1"."DD",NEXT_DAY("A1"."DD" , 'MONDAY')) WEEK_END
        FROM
            (SELECT DD+(level-1) "DD"
            FROM
                (SELECT TO_DATE('01012015','DDMMYYYY') "DD" FROM "SYS"."DUAL" "A2"
                )
                CONNECT BY (DD+(level-1))< TO_DATE('01012016','DDMMYYYY')
            ) "A1"
        ) DAYS ;

--To group all weeks current year and next(if any in end of year)

CREATE OR REPLACE VIEW  CUSTOM_WEEKS AS 
    SELECT T.YEAR_NO,
         T.WEEK_START,
         T.WEEK_END,
         SUM(1) S
    FROM  CUSTOM_DAYS_AND_WEEKS T WHERE YEAR_NO>=2015 
    group by T.YEAR_NO, T.WEEK_START, T.WEEK_END
    ORDER BY 1, 2;

--week start date, end date and week number 

CREATE OR REPLACE VIEW  CUSTOM_WEEKS_NO AS    
SELECT  T.YEAR_NO,
         T.WEEK_START,
         T.WEEK_END,
         DECODE (MOD(ROWNUM,(SELECT COUNT(*)+1 FROM  CUSTOM_WEEKS T WHERE T.YEAR_NO=2015)),0,1,MOD(ROWNUM,(SELECT COUNT(*)+1 FROM  CUSTOM_WEEKS T WHERE T.YEAR_NO=2015))) WEEK_NO
FROM  CUSTOM_WEEKS T;

-- TO check the created Views 

SELECT * FROM  CUSTOM_DAYS_AND_WEEKS;    
SELECT * FROM  CUSTOM_WEEKS;

--week all dates of year with week start date, end date and week number

SELECT D_W.*, W.WEEK_NO 
FROM  CUSTOM_DAYS_AND_WEEKS D_W,
 CUSTOM_WEEKS_NO W
WHERE D_W.WEEK_START=W.WEEK_START
AND D_W.WEEK_END=W.WEEK_END ORDER BY DD DESC;
Ram
  • 845
  • 1
  • 13
  • 26