sysdate与to_date函数有关的问题 [英] issue with to_date function with sysdate

问题描述

我看到了这个问题 所以我对此有一个疑问: 在以下查询中，我在同一日期得到了不同的结果.

I saw this question so I've one doubt regarding this question: I am getting different result with the same date on the following query.

SELECT TO_CHAR(to_date(sysdate, 'DD-MON-yy'), 'DAY'),
  TO_CHAR(to_date(sysdate, 'DD-MON-yyyy'), 'DAY'),
  TO_CHAR(to_date(sysdate, 'DD-MON-rr'), 'DAY'),
  TO_CHAR(to_date(sysdate, 'DD-MON-rrrr'), 'DAY')
FROM dual;

按列输出:

TUESDAY SUNDAY TUESDAY TUESDAY

请帮助我，谢谢.

编辑

我编写了一个简单的过程，该过程通过日期来查找下面给出的日期:

I wrote a simple procedure which passing date for finding the day given below:

SET serveroutput ON;
CREATE OR REPLACE
    PROCEDURE simple_test
      (
        date_in         IN VARCHAR2)
                        IS
      v_date DATE       := to_date(date_in,'dd-mon-yyyy');
      v_day VARCHAR2(10):=TO_CHAR(v_date,'day');
    BEGIN
       dbms_output.put_line('the day of given date is '||v_day);
    END;
    /

EXEC simple_test(sysdate);

EXEC simple_test(sysdate);

anonymous block completed
the day of given date is sunday  

EXEC simple_test('01 -JAN-2013');

EXEC simple_test('01-JAN-2013');

anonymous block completed
the day of given date is tuesday  

为什么会这样?

推荐答案

我想解释一下为什么会得到不同的结果.

I want to explain why you get different results.

请参见 sqlfiddle

如前所述，sysdate被视为DATE类型，并且您在进行隐式转换时

As it is already said, sysdate is seen as DATE type and you are doing an implicit conversion when

select to_date(sysdate, format) from dual;

因为to_date的第一个参数应该是系统执行的varchar类型:

because first parameter of to_date should be varchar type the system does:

select to_date(to_char(sysdate), format) from dual;

因为您的隐式日期格式为'DD-MON-YY'，所以查询进入:

because your implicit date format is 'DD-MON-YY', your query goes into:

SELECT TO_CHAR(to_date('01-JAN-13', 'DD-MON-yy'), 'DAY'),
  TO_CHAR(to_date('01-JAN-13', 'DD-MON-yyyy'), 'DAY'),
  TO_CHAR(to_date('01-JAN-13', 'DD-MON-rr'), 'DAY'),
  TO_CHAR(to_date('01-JAN-13', 'DD-MON-rrrr'), 'DAY')
FROM dual;

第二个to_date，因为yyyy是整整千年的格式，因此将其移至"01-JAN-0013"，即13AD，可能是SUNDAY:)

the second to_date, because yyyy is a full thousands year format, goes to '01-JAN-0013' which is 13AD and probably is SUNDAY :)

这篇关于sysdate与to_date函数有关的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！