Oracle-更改每周的开始日期-周二 [英] Oracle - Change start day of Week - Tuesday

问题描述

我想在Oracle中更改星期的开始日期，并且需要从每日/每小时的数据中计算出每周的一些报告. 通过很多Google搜索，我只有NLS_TERROTORY选项，但我不知道该如何设置为TUESDAY.即使有领土，将来如何更改为不同的日期.预先感谢！

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!

推荐答案

您可以创建一个包，以包含与新的一周开始有关的自定义函数:

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

注意:一年中的第几周基于一年中的第一周，其中包含一周中的第一天(例如星期二)，因此，如果1月1日是星期三，则该年的第一周将是从1月7日开始).如果您希望使用其他逻辑，则需要修改WEEK_OF_YEAR函数.

SQL小提琴

Oracle 11g R2架构设置:

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;
/

查询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    |

这篇关于Oracle-更改每周的开始日期-周二的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！