11

For my project I need to have an absolute numerical correspondence between days of the week and 1...7 values.

As you probably know the association between days and numbers can vary according to the locale, for example in Germany Monday is 1 and Sunday is 7, while in US Monday is 2 while Sunday is 1.

So, searching for a solution, I found the following code which seems working regardless of the locale, assigning Monday=1...Sunday=7:

1 + TRUNC (date) - TRUNC (date, 'IW')

Can someone explain me how does it work? In particular I just can't understand what this instruction:

TRUNC (date, 'IW')

exactly does.

Lalit Kumar B
  • 43,345
  • 10
  • 82
  • 112
Phate
  • 4,750
  • 10
  • 51
  • 98
  • trunc(date,'IW') returns the first day of the week and is not affected by the nls_territory settings, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084. Only the format string DAY, DY, and D are affected. – Frank Ockenfuss Sep 16 '15 at 08:38
  • 1
    @FrankOckenfuss um, no. IW returns the first day of the ISO week (Monday) and is therefore totally unaffected by the nls_territory_settings. – Boneist Sep 16 '15 at 08:41
  • @Boneist You are right, my first answer was to fast :-D – Frank Ockenfuss Sep 16 '15 at 08:42

2 Answers2

14

TRUNC(DATE,'IW') returns the first day of the week. For me TRUNC(SYSDATE,'IW) returns Monday. Today is Tuesday Feb 21. Subtract from that TRUNC(SYSDATE,'IW') which would be Monday the 20th, and you'll get 1 (because 21-20=1). Add 1 onto that as you do in the beginning of your equation and you get 2, which we associate with Tuesday.

spassas
  • 4,208
  • 2
  • 29
  • 37
John
  • 156
  • 1
  • 2
3

The very basic concept of ISO week is to make it NLS territory independent.

From documentation,

Week of year (1-52 or 1-53) based on the ISO standard.

A week starts on a Monday and ends on a Sunday.

Lalit Kumar B
  • 43,345
  • 10
  • 82
  • 112
  • And just for curiosity, [the link to the Overview of Globalization Support](https://docs.oracle.com/cd/B28359_01/server.111/b28298/ch1overview.htm#NLSPG001) – Andrey Hartung Mar 21 '19 at 12:53