Oracle 奇怪行为字符串与星期几的比较

发布于 2024-10-17 03:44:02 字数 623 浏览 3 评论 0原文

虽然下面的代码打印“错误的星期四”，（10-FEB 是星期四）

BEGIN
  IF to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day')='Thursday' THEN
    dbms_output.put_line('Correct');

  ELSE
    dbms_output.put_line('Wrong '||to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day'));
  END IF;
END;

下面的代码打印“正确”，（09-FEB 是星期三）

BEGIN
  IF to_char(to_date('09-FEB-2011','DD-MON-YYYY'),'Day')='Wednesday' THEN
    dbms_output.put_line('Correct');

  ELSE
    dbms_output.put_line('Wrong '||to_char(to_date('09-FEB-2011','DD-MON-YYYY'),'Day'));
  END IF;
END;

我一直试图弄清楚这一点，但我做不到。任何帮助将不胜感激。提前致谢。

原文

While the below code prints 'Wrong Thursday',(10-FEB is a Thursday)

BEGIN
  IF to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day')='Thursday' THEN
    dbms_output.put_line('Correct');

  ELSE
    dbms_output.put_line('Wrong '||to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day'));
  END IF;
END;

The following prints 'Correct',(09-FEB is a Wednesday)

BEGIN
  IF to_char(to_date('09-FEB-2011','DD-MON-YYYY'),'Day')='Wednesday' THEN
    dbms_output.put_line('Correct');

  ELSE
    dbms_output.put_line('Wrong '||to_char(to_date('09-FEB-2011','DD-MON-YYYY'),'Day'));
  END IF;
END;

I've been trying to figure this out but I couldn't. Any help would be much appreciated. Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

请恋爱 2024-10-24 03:44:02

to_char 默认情况下以空格填充：

SQL> BEGIN
  2    dbms_output.put_line('x' ||
  3                         to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day')||
  4                         'x');
  5  END;
  6  /

xThursday x

使用修饰符 fm 防止填充：

SQL> BEGIN
  2    IF to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'fmDay')='Thursday' THEN
  3      dbms_output.put_line('Correct');
  4    ELSE
  5      dbms_output.put_line('Wrong '||to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'fmDay'));
  6    END IF;
  7  END;
  8  /

Correct

PL/SQL procedure successfully completed

to_char by default is space-padded:

SQL> BEGIN
  2    dbms_output.put_line('x' ||
  3                         to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day')||
  4                         'x');
  5  END;
  6  /

xThursday x

Use the modifier fm to prevent the padding:

SQL> BEGIN
  2    IF to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'fmDay')='Thursday' THEN
  3      dbms_output.put_line('Correct');
  4    ELSE
  5      dbms_output.put_line('Wrong '||to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'fmDay'));
  6    END IF;
  7  END;
  8  /

Correct

PL/SQL procedure successfully completed

回复收藏 0 原文

江湖正好 2024-10-24 03:44:02

提示：

select length(to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day')) from dual;

返回 9，尽管 length('Thursday') 为 8。

更多信息可在 Oracle 文档中找到，网址为 http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34510 :
字符元素 MONTH、MON、DAY 和 DY 用尾随空格填充至最长完整月份名称、最长缩写月份名称、最长完整日期名称或分别是由 NLS_DATE_LANGUAGE 和 NLS_CALENDAR 参数值确定的有效名称中最长的缩写日期名称。例如，当 NLS_DATE_LANGUAGE 为 AMERICAN 并且 NLS_CALENDAR 为 GREGORIAN（默认值）时，MONTH 的最大元素为 SEPTEMBER，因此 MONTH 格式元素的所有值都将填充为 9 个显示字符。 NLS_DATE_LANGUAGE 和 NLS_CALENDAR 参数的值在 TO_CHAR 和 TO_* 日期时间函数的第三个参数中指定，或者从当前会话的 NLS 环境中检索。

强调我的。

Hint:

select length(to_char(to_date('10-FEB-2011','DD-MON-YYYY'),'Day')) from dual;

returns 9, although length('Thursday') is 8.

More information is found on Oracle's docu at http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34510:
The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name, or the longest abbreviated day name, respectively, among valid names determined by the values of NLS_DATE_LANGUAGE and NLS_CALENDAR parameters. For example, when NLS_DATE_LANGUAGE is AMERICAN and NLS_CALENDAR is GREGORIAN (the default), the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to nine display characters. The values of the NLS_DATE_LANGUAGE and NLS_CALENDAR parameters are specified in the third argument to TO_CHAR and TO_* datetime functions or they are retrieved from the NLS environment of the current session.

Emphasis mine.

回复收藏 0 原文