Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_TERRITORY = 'Germany';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT LEVEL AS day_number,
       TO_CHAR(TRUNC(SYSDATE, 'IW') + LEVEL - 1, 'fmDAY') AS day
FROM   DUAL
CONNECT BY LEVEL <= 7;


DAY_NUMBER
DAY


1
MONDAY

2
TUESDAY

3
WEDNESDAY

4
THURSDAY

5
FRIDAY

6
SATURDAY

7
SUNDAY


SELECT SYSDATE,
       TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 AS day_number,
       TO_CHAR(SYSDATE, 'fmDAY') AS day
FROM   DUAL;


SYSDATE
DAY_NUMBER
DAY


2023-09-27 08:34:03
3
WEDNESDAY


SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'D') AS to_char_day_number,
       (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TERRITORY') AS territory
FROM   DUAL;


SYSDATE
TO_CHAR_DAY_NUMBER
TERRITORY


2023-09-27 08:34:03
3
GERMANY


ALTER SESSION SET NLS_TERRITORY = 'America';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT SYSDATE,
       TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 AS day_number,
       TO_CHAR(SYSDATE, 'fmDAY') AS day
FROM   DUAL;


SYSDATE
DAY_NUMBER
DAY


2023-09-27 08:34:03
3
WEDNESDAY


SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'D') AS to_char_day_number,
       (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TERRITORY') AS territory
FROM   DUAL;


SYSDATE
TO_CHAR_DAY_NUMBER
TERRITORY


2023-09-27 08:34:03
4
AMERICA


ALTER SESSION SET NLS_TERRITORY = 'Egypt';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT SYSDATE,
       TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 AS day_number,
       TO_CHAR(SYSDATE, 'fmDAY') AS day
FROM   DUAL;


SYSDATE
DAY_NUMBER
DAY


2023-09-27 08:34:03
3
WEDNESDAY


SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'D') AS to_char_day_number,
       (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TERRITORY') AS territory
FROM   DUAL;


SYSDATE
TO_CHAR_DAY_NUMBER
TERRITORY


2023-09-27 08:34:03
5
EGYPT


ALTER SESSION SET NLS_TERRITORY = 'Bangladesh';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT SYSDATE,
       TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 AS day_number,
       TO_CHAR(SYSDATE, 'fmDAY') AS day
FROM   DUAL;


SYSDATE
DAY_NUMBER
DAY


2023-09-27 08:34:03
3
WEDNESDAY


SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'D') AS to_char_day_number,
       (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TERRITORY') AS territory
FROM   DUAL;


SYSDATE
TO_CHAR_DAY_NUMBER
TERRITORY


2023-09-27 08:34:03
6
BANGLADESH