What is difference between TO_CHAR and TO_DATE

Question

I'm running two queries

select TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS PM') from dual; It displays date with exact time.
select TO_DATE(SYSDATE, 'DD-MON-YYYY HH:MI:SS PM') from dual; It displays date with default time 12:00:00 AM.

I do not understand TO_CHAR and TO_DATE usage. How to display date with exact time by using TO_DATE

`TO_DATE()` converts a `varchar` to a `date`. It makes no sense whatsoever to call it on a value that is *already* a date. — , Feb 04 '16 at 11:37
@a_horse_with_no_name what about if you want to change a date format? — sagi, Feb 04 '16 at 11:55
Then you want to convert a `date` to a `varchar` so you need `to_char()`. **Never**, ever (really: never) call `to_date()` on a `date` value. — , Feb 04 '16 at 11:59
Possible duplicate of [to\_char and to\_date are returning different output](http://stackoverflow.com/questions/16796350/to-char-and-to-date-are-returning-different-output) — sagi, Feb 04 '16 at 12:08

score 1 · Answer 1 · answered Feb 04 '16 at 11:35

to_char function is used to convert the given data into character....

SQL> SELECT TO_CHAR(SYSDATE, 'dd/mm/yyyy') FROM dual;

TO_CHAR(SY
------------------
04/04/2012

to_date is used to convert the given data into date data formate data type....

eg: to_date('070903', 'MMDDYY') would return a date value of July 9, 2003.

1 Answers1