使用Oracle SQL，一个星期几和星期几的输出日数如何? [英] Using Oracle SQL, how does one output day number of week and day of week?

问题描述

使用Oracle SQL，如何为以下项目创建结果集:

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

• 星期几(1-7)的数字
• 日期名称(星期一，星期二，星期三等)

例如:

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

推荐答案

弗洛林的答案是我会怎么做，但是您需要对

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

to_char()和nls_date_language 和星期几，等等在全球化支持指南中.

这篇关于使用Oracle SQL，一个星期几和星期几的输出日数如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！