I am trying to find the weekday for Sunday to Saturday. In oracle by default Monday is the first day of the week. So When I calculate weekday with following query

Select    to_char(sysdate, 'D')  from dual

as today is 09/12/2022 this is giving me result as 5 as week start from Monday. I want this should be calculated as week start from Sunday. Ideally, answer should be 6 if this week start from Sunday.

Is there any easy way to do it, I can use case statement, but that is not a good way. Please help.

CodePudding user response：

Talk to your DBA to fix territory. If it was set to e.g. India, then you wouldn't have to calculate anything:

SQL> alter session set nls_territory = 'India';

Session altered.

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

T
-
6

SQL>

CodePudding user response：

In Oracle, the first day of a week is determined by NLS_TERRITORY parameter. So use a value of NLS_TERRITORY where Sunday is the first weekday, for example, AMERICA.

alter session set nls_territory=RUSSIA

select to_char(sysdate, 'D')
from dual

TO_CHAR(SYSDATE,'D')
5

alter session set nls_territory=AMERICA

select to_char(sysdate, 'D')
from dual

TO_CHAR(SYSDATE,'D')
6

fiddle

CodePudding user response：

Option 1: NLS_TERRITORY session parameter

In Oracle, the day of the week depends on the NLS_TERRITORY session setting:

SELECT TO_CHAR(DATE '2022-12-11', 'D') AS day, -- a sunday
       (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TERRITORY')
         AS territory
FROM   DUAL;

Outputs different values for different parameters. I.e., if you use:

ALTER SESSION SET NLS_TERRITORY='America';
ALTER SESSION SET NLS_TERRITORY='Germany';
ALTER SESSION SET NLS_TERRITORY='India';
ALTER SESSION SET NLS_TERRITORY='Oman';
ALTER SESSION SET NLS_TERRITORY='Bangladesh';

and run the query for each then the outputs are:

DAY	TERRITORY
1	AMERICA
7	GERMANY
1	INDIA
2	OMAN
3	BANGLADESH

So you could just pick the correct territory for your database, i.e.:

ALTER SESSION SET NLS_TERRITORY='India';

---

Option 2: Compare to the start of the ISO-week

Or if you want to be independent of the session settings then you can compare it to the start of the ISO-week (which is always midnight Monday) and offset by one day to change the comparison from Monday-Sunday to Sunday-Saturday:

SELECT TRUNC(SYSDATE   1) - TRUNC(SYSDATE   1, 'IW')   1 AS day
FROM   DUAL;

Which, for today (Friday) outputs:

DAY
6

fiddle