Display date differently on different servers

Question

When I run the following query on the desktop

Select to_char(join_date, 'DD-MON-YYYY') From students group by to_char(join_date, 'DD-MON-YYYY'); 

I get the following output:

 23-Oct-2012 25-Oct-2012 23-Oct-2012 23-Oct-2012 23-Oct-2012 26-Oct-2012 23-Oct-2012 24-Oct-2012 23-Oct-2012 

But if I run it on another server, the output will look like:

 23-Oct. -2012 25-Oct. -2012 23-Oct. -2012 23-Oct. -2012 23-Oct. -2012 26-Oct. -2012 23-Oct. -2012 24-Oct. -2012 23-Oct. -2012 

Any ideas what could be causing this? The second server works in another country from me, so its regional settings may be different.

Update:

NLS_DATE_FORMAT is DD-Mon-RRRR on both servers.

Answer 1

When the oracle retrieves the date field from the database and shows it to you, a silent translation is performed . The format template for this conversion is set in the oracle configuration. Indication of oracle document :

The default date format for a date value in Oracle is derived from the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE initialization parameters

If NLS_DATE_FORMAT is DD-Mon-RRRR on both servers , check NLS_DATE_LANGUAGE .

Answer 2

You were not mistaken in the code as:

 select to_char(join_date,'dd-mon.-yyyy') from students; 

This will produce the results:

 23-Oct.-2012 

Answer 3

As danihp suggests, the difference probably depends on the NLS_LANGUAGE parameter, which is probably derived from the higher NLS parameter. You can override this to get consistent results, although this is not necessarily a good idea - as described in the globalization support guide . Executing these queries should produce consistent results on two servers (assuming one of yours is French from the data you showed):

 select to_char(join_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=FRENCH') from students ...; select to_char(join_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') from students ...; 

At the heart of the confusion is what you expect from MON . As indicated in the SQL reference , MON is the "month abbreviation"; this means that it is not necessarily an abbreviation of three characters. How the full name of the month is shortened depends on the NLS settings.

In English, all month names can be abbreviated to three characters unambiguously:

 select level as l, to_char(to_date(level, 'MM'), 'Month', 'NLS_DATE_LANGUAGE=ENGLISH'), to_char(to_date(level, 'MM'), 'Mon', 'NLS_DATE_LANGUAGE=ENGLISH') from dual connect by level <= 12 order by 1 L TO_CHAR(TO_DATE(LEVEL,'MM'),'MONTH', TO_CHAR(TO_D ---------- ------------------------------------ ------------ 1 January Jan 2 February Feb 3 March Mar 4 April Apr 5 May May 6 June Jun 7 July Jul 8 August Aug 9 September Sep 10 October Oct 11 November Nov 12 December Dec 

In French, this is not so:

 select level as l, to_char(to_date(level, 'MM'), 'Month', 'NLS_DATE_LANGUAGE=FRENCH'), to_char(to_date(level, 'MM'), 'Mon', 'NLS_DATE_LANGUAGE=FRENCH') from dual connect by level <= 12 order by 1; L TO_CHAR(TO_DATE(LEVEL,'MM'),'MONTH', TO_CHAR(TO_DATE(LEVE ---------- ------------------------------------ -------------------- 1 Janvier Janv. 2 Février Févr. 3 Mars Mars 4 Avril Avr. 5 Mai Mai 6 Juin Juin 7 Juillet Juil. 8 Août Ao 9 Septembre Sept. 10 Octobre Oct. 11 Novembre Nov. 12 Décembre Déc. 

Clearly, using three-letter abbreviations would create difficulties for Juin and Juillet. Presumably, designating an abbreviation with a period is a cultural thing, and with several months requiring four characters to be distinctive, this reduces the length of the MON format to five characters.

It may seem a little surprising that the format model saves this as a fixed width, so you have space in the middle for shorter abbreviations, as in your original data. I am sure that this could be argued in any case, and it seems that it is being applied consistently. I do not know how to stop this automatically.