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    |