to_date function with sysdate

Question

select TO_CHAR(to_date(sysdate, 'DD-MON-YYYY'), 'DAY') FROM DUAL;

When I run this query the output was : SUNDAY. But we know today is Tuesday(1-1-2013). And

then changed the query as

select TO_CHAR(to_date('01-JAN-2013', 'DD-MON-YYYY'), 'DAY') FROM DUAL;

answer was :TUESDAY.

then Changed query as

select TO_CHAR(to_date(sysdate+1, 'DD-MON-YYYY'), 'DAY') FROM DUAL;

answer is :MONDAY.

When I using the sysdate why it is show SUNDAY as output?

I am new in oracle db. Please help me.

SYSDATE is already a date. Why would you think it was necessary to use TO_DATE() on it? — APC, Jan 01 '13 at 06:06
@APC Now I am studying basics of the oracle, and going through this just tried some examples and this query developed myself from some other query. — ıllıllı lק ıllıllı, Jan 01 '13 at 06:31
when I using SELECT TO_CHAR(TO_DATE(sysdate, 'DD-MM-RRRR'), 'DAY') from dual; The Ans: TUESDAY — ıllıllı lק ıllıllı, Jan 01 '13 at 06:43

score 6 · Accepted Answer · edited Nov 27 '18 at 03:38

6

use this:

 select TO_CHAR(sysdate, 'DAY') FROM DUAL;

you are using this :

 to_date(sysdate, 'DD-MON-YYYY')

which is giving you date=1/1/0013 which is sunday

edited Nov 27 '18 at 03:38

Jeffrey Kemp

answered Jan 01 '13 at 05:44

Nipun Jain

score 2 · Answer 2 · answered Jan 01 '13 at 05:30

Please refer the documentation for sysdate here. Sysdate is already a date data type.

Your example query is inappropriate as to_date function takes first parameter as String not date.

Try the simple query below:

     select TO_CHAR(sysdate, 'DAY') FROM DUAL;

This should return TUESDAY as output.

Ishani Gupta · Answer 3 · 2016-02-25T06:11:20.690

2

To_date is used to convert a strin to date. As sysdate is already a date, one must not add add to_date.

edited Feb 25 '16 at 06:11

answered Feb 24 '16 at 08:37

Ishani Gupta

Strictly speaking, sysdate contains both date AND time components. If you, for example, compare sysdate to a DATE field value of today, = comparison will fail. You will need to_date() to make = match. – JustAMartin May 31 '18 at 12:46

3 Answers3