0

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

Fab
  • 1,145
  • 7
  • 20
  • 40
  • 2
    Please read about NLS settings. What is displayed is property of session not physical representation of date. – Kacper Jan 26 '17 at 11:28
  • @Kacper So, the proper way to convert a date with a numerical month to a date with a string month is the first one? – Fab Jan 26 '17 at 11:32
  • 1
    Date is date it has neither numerical nor string month. It is only NLS setting how date is printed. – Kacper Jan 26 '17 at 11:35

4 Answers4

4

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
MT0
  • 143,790
  • 11
  • 59
  • 117
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';
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

The output format of TO_CHAR is not correct, try:

SELECT TO_CHAR(TO_DATE('01-01-2015','DD-MM-YYYY'),'DD-MM-YYYY') FROM DUAL;
Dharman
  • 30,962
  • 25
  • 85
  • 135
Alfonso
  • 1
0

Oracle TO_DATE: is used to convert a character string to a date format.

and related to your concern; you need to alter your session like below:

alter session set nls_date_format='DD-MM-YYYY'

in your apps right after the connect.

So now if you run again your query :

SELECT TO_DATE ('01-01-2015', 'DD-MM-YYYY')
FROM DUAL;

the result would be as expected:

01-01-2015

Hope that will help.

Hassan Abdelrahman
  • 27
  • 2