使用Oracle SQL，如何输出星期几和星期几？

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

提问by Bit_hunter

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

使用 Oracle SQL，您如何为以下对象创建结果集：

• Number for the Day Of The Week (1-7)
• Name of the day (Monday, Tuesday, Wednesday, etc.)

• 星期几 (1-7)
• 日期名称（星期一、星期二、星期三等）

For example:

例如：

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

回答by Alex Poole

Florin's answer is how I'd do it, but you need to be a little careful about NLSsettings. 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:

弗洛林的回答是我会怎么做，但你需要对NLS设置小心一点。星期几受 NLS 领土的影响，所以如果我像在美国一样运行它，它会起作用：

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_languageand day of the week, and more in the globalisation support guide.

文档to_char()与nls_date_language和星期，多在全球化的支持指导。

回答by Florin Ghita

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

回答by igr

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