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;
output as column-wise:
TUESDAY SUNDAY TUESDAY TUESDAY
please help me, Thanks in advance.
EDIT
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);
anonymous block completed
the day of given date is sunday
EXEC simple_test('01-JAN-2013');
anonymous block completed
the day of given date is tuesday
why does this happened?
Best Answer
I want to explain why you get different results.
See this sqlfiddle
As it is already said, sysdate is seen as
DATE
type and you are doing an implicit conversion whenbecause first parameter of to_date should be varchar type the system does:
because your implicit date format is
'DD-MON-YY'
, your query goes into:the second to_date, because
yyyy
is a full thousands year format, goes to '01-JAN-0013' which is 13AD and probably is SUNDAY :)