TO_DATE function in ORACLE

Question

I was trying the TO_DATE function. Specifically, I noted that the following queries

1. SELECT TO_CHAR(TO_DATE('01-01-2015','DD-MM-YYYY'),'DD-MON-YY') FROM DUAL
2. SELECT TO_DATE('01-01-2015','DD-MM-YYYY') FROM DUAL

have the same output: 01-JAN-2015.

Why does the TO_DATE function return the month in its abbreviated form? My expected output for the second query is something like 01-01-2015 (simply, a TYPE conversion, NOT a format conversion).

Am I wrong?

Thanks

Answer 1

Dates do not have a format - they are represented by 7- or 8-bytes.

SELECT DUMP( SYSDATE ) FROM DUAL;

Might output:

Typ=13 Len=8: 220,7,11,26,16,41,9,0

This format is very useful for computers to compare dates but not so useful to people; so, when the SQL client (SQL/plus, SQL Developers, TOAD, etc) displays a date it does not display the the bytes but displays it as a string.

It does this by making an implicit call to TO_CHAR() (or some other internal method of stringifying dates) and uses a default format mask to perform this conversion.

SQL/Plus and SQL Developer will use the user's session parameter NLS_DATE_FORMAT to perform this conversion - see this answer regarding this.

So your second query is implicitly being converted to do something approaching this (but, almost certainly, more efficiently):

SELECT TO_CHAR(
         TO_DATE('01-01-2015','DD-MM-YYYY'),
         ( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
       )
FROM   DUAL

Answer 2

The default output format of DATE value, resp TO_DATE() function is set by NLS_DATE_FORMAT value. You can verify it with this query:

SELECT * 
FROM V$NLS_PARAMETERS 
WHERE PARAMETER = NLS_DATE_FORMAT';

You can change it on session level for example with

alter session set NLS_DATE_FORMAT = 'DD-MM-YYYY';