2

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?

Community
  • 1
  • 1
ajmalmhd04
  • 2,582
  • 6
  • 23
  • 41
  • 3
    Read the answers to that other question carefully and you will see that sysdate is already a date. Don't use to_date(sysdate, ...) - just use sysdate ... – Lord Peter Jan 01 '13 at 08:00
  • possible duplicate of [to\_date function with sysdate](http://stackoverflow.com/questions/14108022/to-date-function-with-sysdate) – Jonathan Leffler Jan 01 '13 at 12:44

2 Answers2

4

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 when

select to_date(sysdate, format) from dual;

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

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

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;

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

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
  • yeah.. you're right and thanks.. The thing according my procedure is best to use ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; which should be the same parameters which i mentioned inside the procedure. (inorder to take full year as YYYY or RRRR ) eg:2013 insted of 0013. – ajmalmhd04 Jan 22 '13 at 09:17
1

SYSDATE is already a date. So if you write:

TO_DATE(SYSDATE, 'DD-MON-yy')

you are already doing two conversions, an implict one from date to string and an explicit one from string to date. The implicit one is the problem as you can't specify the date format. (Instead it's taken from the session's current settings.)

So the solution is to get rid of the unnecessary conversion an just write:

SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;
Codo
  • 75,595
  • 17
  • 168
  • 206