CODEWITHSUNDEEP
sqloracle-database
Bit_hunter
Bit_hunter

Reputation: 819

Using Oracle SQL, how does one output day number of week and day of week?

Using Oracle SQL, how do you create a result set for:

For example:

DAY   NAME
1     Monday
2     Tuesday
3     Wednesday
4     Thursday
5     Friday
6     Saturday
7     Sunday

Upvotes: 17

Views: 73582

Answers (6)

Chris Saxon
Chris Saxon

Reputation: 9875

From Oracle Database 23ai you can create an enumeration domain to list the days of the week:

create domain days_of_week as enum ( 
  monday,   tuesday, wednesday, 
  thursday, friday,  saturday, 
  sunday 
);

select * from days_of_week;

ENUM_NAME ENUM_VALUE
--------- ----------
MONDAY             1
TUESDAY            2
WEDNESDAY          3
THURSDAY           4
FRIDAY             5
SATURDAY           6
SUNDAY             7

The values start at one and increase by one for each item. You can supply your own starting value. For example, this start with Monday having the value zero:

drop domain if exists days_of_week;
create domain days_of_week as enum ( 
  monday = 0, tuesday, wednesday, 
  thursday,   friday,  saturday, 
  sunday 
);

select * from days_of_week;

ENUM_NAME ENUM_VALUE
--------- ----------
MONDAY             0
TUESDAY            1
...
SUNDAY             6

Or you can use string values for each day. Here the values are the two-letter abbreviations of the day name:

drop domain if exists days_of_week;
create domain days_of_week as enum ( 
  monday    = 'MO', tuesday   = 'TU', 
  wednesday = 'WE', thursday  = 'TH', 
  friday    = 'FR', saturday  = 'SA', 
  sunday    = 'SU'
);

select * from days_of_week;

ENUM_NAME EN
--------- --
MONDAY    MO
TUESDAY   TU
WEDNESDAY WE
THURSDAY  TH
FRIDAY    FR
SATURDAY  SA
SUNDAY    SU

You can also link many names with one value. The built-in enum day_enum_d lists the full names and three letter abbreviations for each name:

select * from sys.day_enum_d;

ENUM_NAME ENUM_VALUE
--------- ----------
MONDAY             1
MON                1
TUESDAY            2
TUE                2
WEDNESDAY          3
...

Upvotes: 0

MT0
MT0

Reputation: 168623

When trying to get the day number from a date, you can avoid the issues that the NLS_TERRITORY settings raise when using TO_CHAR(dt, 'D') and, instead, compare the day to the start of the ISO Week (which is always midnight Monday):

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

Which outputs:

DAY_NUMBER DAY
5 FRIDAY

Note: you can also use fmDAY to automatically strip trailing white-space from the day name. If you use DAY (without the fm modifier) then all the day names will be padded to an equal length; which for English would mean that Friday would be right-padded to the same length as Wednesday.

Comparing using the ISO week to using TO_CHAR:

ALTER SESSION SET NLS_TERRITORY = 'GERMANY';

SELECT TO_CHAR(SYSDATE, 'D') AS dn_tochar
       TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 AS dn_isoweek
FROM   DUAL

Outputs:

DN_TOCHAR DN_ISOWEEK
5 5

Which is good; as Oracle considers that Germany's week starts on a Monday so Friday is the 5th day of the week.

But the same query in a different territory:

ALTER SESSION SET NLS_TERRITORY = 'BANGLADESH';

SELECT TO_CHAR(SYSDATE, 'D') AS dn_tochar
       TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 AS dn_isoweek
FROM   DUAL

Outputs:

DN_TOCHAR DN_ISOWEEK
1 5

And does not give the same answer for the TO_CHAR version as Oracle considers that Bangladesh's week starts on a Friday so it would be day 1 of their week.

Similarly, amongst other countries, Egypt's week starts on a Saturday and America's week starts on a Sunday so all their values will be off if you use TO_CHAR; but not if you use the ISO week.

But if you just want to generate 7 rows with each day of the week, then you can simplify the problem and use a hierarchical query to generate the day numbers and then offset the date from the start of the ISO week:

SELECT LEVEL AS day_number,
       TO_CHAR(
         TRUNC(SYSDATE, 'IW') + LEVEL - 1,
         'fmDAY',
         'NLS_DATE_LANGUAGE=English'
       ) AS day
FROM   DUAL
CONNECT BY LEVEL <= 7;

Which always outputs:

DAY_NUMBER DAY
1 MONDAY
2 TUESDAY
3 WEDNESDAY
4 THURSDAY
5 FRIDAY
6 SATURDAY
7 SUNDAY

db<>fiddle here

Upvotes: 6

mauserez
mauserez

Reputation: 11

Without alter session

 CASE 
    WHEN to_char(sysdate, 'd')-1 = 0 THEN 7 
    ELSE to_char(sysdate, 'd')-1 
 END

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191560

Florin's answer is how I'd do it, but you need to be a little careful about NLS settings. The day of the week is affected by the NLS territory, so if I run this as if I'm in the US it works:

alter session set nls_territory = 'AMERICA';

select to_char(sysdate, 'D') as d, to_char(sysdate, 'Day') as day from dual;

D DAY
- ------------------------------------
6 Friday

select level as dow,
    to_char(trunc(sysdate ,'D') + level, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Monday
  2 Tuesday
  3 Wednesday
  4 Thursday
  5 Friday
  6 Saturday
  7 Sunday

But the same query run in the UK is a day off:

alter session set nls_territory = 'UNITED KINGDOM';

select to_char(sysdate, 'D') as d, to_char(sysdate, 'Day') as day from dual;

D DAY
- ------------------------------------
5 Friday

select level as dow,
    to_char(trunc(sysdate ,'D') + level, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Tuesday
  2 Wednesday
  3 Thursday
  4 Friday
  5 Saturday
  6 Sunday
  7 Monday

... and I need to adjust the calculation to correct for that:

select level as dow,
    to_char(trunc(sysdate ,'D') + level - 1, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Monday
  2 Tuesday
  3 Wednesday
  4 Thursday
  5 Friday
  6 Saturday
  7 Sunday

You can also specify the language used for the day names separately if you want:

select level as dow,
    to_char(trunc(sysdate ,'day') + level - 1, 'Day',
        'NLS_DATE_LANGUAGE=FRENCH') as day
from dual
connect by level <= 7;

DOW DAY
--- --------------------------------
  1 Lundi
  2 Mardi
  3 Mercredi
  4 Jeudi
  5 Vendredi
  6 Samedi
  7 Dimanche

Documentation for to_char() with nls_date_language and day of the week, and more in the globalisation support guide.

Upvotes: 29

Florin Ghita
Florin Ghita

Reputation: 17643

select level as dow, 
    to_char(level+trunc(sysdate,'D'),'Day') as day
from dual
connect by level <= 7;

Upvotes: 2

igr
igr

Reputation: 3499

Select 1, 'Monday' from dual union all select 2, 'Tuesday' from dual ...

Upvotes: -2

Related Questions