Oracle - Change start day of Week - Tuesday

Question

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!

Answer 1

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    |

Answer 2

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>

Answer 3

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.

Answer 4

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;