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

3 回答

• 2

  Florin 's answer is how I' d做到了，但你需要对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
  

  但在英国运行的同一个查询是休息日：

  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
  

  ......我需要调整计算以纠正错误：

  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
  

  如果需要，您还可以单独指定用于日期名称的语言：

  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() with nls_date_language和day of the week的文档，以及the globalisation support guide中的更多文档 .

  回复于 2024-04-29T10:25:34+08:00
• -2

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

  回复于 2024-04-29T10:25:34+08:00
• 20

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

  回复于 2024-04-29T10:25:34+08:00