Week start from Sunday while calculating weekday

Question

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.

"In oracle by default Monday is the first day of the week." This is incorrect as the first day of the week depends on the `NLS_TERRITORY` session parameter which any user can change in their own session. — MT0, Dec 09 '22 at 11:57

Littlefoot · Answer 1 · 2022-12-09T11:47:23.077

0

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>

edited Dec 09 '22 at 11:47

answered Dec 09 '22 at 11:35

Littlefoot

131,892
15
35
57

what if sysdate is sunday this will give me 8, in that case it should show 1 – deepankar srigyan Dec 09 '22 at 11:44
True; that was a stupid suggestion. Fix the territory (or use CASE, but that's something you'd want to avoid anyway). – Littlefoot Dec 09 '22 at 11:47

score 0 · Answer 2 · answered Dec 09 '22 at 11:48

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

MT0 · Answer 3 · 2022-12-09T11:59:25.270

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

score 0 · Answer 4 · answered Dec 09 '22 at 12:52

0

I found the answer to this, which is dead easy

select mod(to_char(sysdate,'D'),7)+1 as Weekday from Dual

answered Dec 09 '22 at 12:52

deepankar srigyan

47
1
9

As commented in the question, this is **not default** value, but `NLS_TERRITORY` specific. This code will produce different result depending on this setting. You may experiment using, for example, [this answer](https://stackoverflow.com/a/74742762/2778710). – astentx Dec 09 '22 at 14:09

Week start from Sunday while calculating weekday

4 Answers4

Option 1: NLS_TERRITORY session parameter

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

Option 1: `NLS_TERRITORY` session parameter