Understanding TO_CHAR to convert dates in oracle

Question

I have an oracle data problem which i would like to understand.

So below are some queries which i executed and on comments are the outputs.

SELECT SYSDATE FROM DUAL; --19-DEC-17 
SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL; -- 19/12/2017
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL; -- 12/19/2017

My question is what kind of date format does the to_char method expecting as the first parameter ? is there a default date format we need to pass?

So i tried this below query as well which throws an error. if some expert could explain me on how the to_char is working, it would be great.

SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL;

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Answer 1

This

SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL;

doesn't make sense. '19/12/2017' is a string, so there's no use in converting it to yet another string (as you used TO_CHAR), not to mention format mask.

It would be OK if you used TO_DATE in this statement, which says that you want to convert a string '19/12/2017' into a date value, using 'dd/mm/yyyy' format mask.

That's what you did in the first part of your message. SYSDATE is a function which returns DATE data type value, so you applied TO_CHAR to it in order to display it in a format you want. Have a look at Format Models, here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

Answer 2

In your query, what you're using is a string value. Instead, if you want to display using TO_CHAR(), you need to use a datetime type string. In your case, '19/12/2017' is a pure string. Please convert it into date type using TO_DATE() function.

SELECT TO_CHAR('19/12/2017','DD/MM/YYYY') FROM DUAL;  // error

Quoting from the Oracle Docs for TO_CHAR (datetime):

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:

DATE values are converted to values in the default date format.

TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.

TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.

Similarly, the function TO_CHAR() accepts numbers as well.

Quoting from the Oracle Docs for TO_CHAR (number):

TO_CHAR (number) converts n to a value of VARCHAR2 datatype, using the optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.