I'm using Oracle 10g XE on Windows Vista and I'm curious as to what determines the date format.
According to the globalization section in the Oracle installation guide the NLS_DATE format is dependent on the NLS_LANGUAGE setting. In my database the NLS_LANGUAGE is American, the NLS_TERITORY is America and all other NLS_* parameters are blank (default).
From that I would expect the default date format to be mon-dd-yyyy.
However when I run SELECT sysdate FROM dual the date is reported as dd/mm/yyyy and this was the default format when I used SQL Developer and when I used the command line SQL*Plus. Obviously something else is determining the date format, but what?
Your settings are dependent on NLS_LANG on the client.
They are derived from (assuming Windows) the regional settings of the O/S.
This means in Western Europe the default 'American_America.WE8MSWIN1252'.
This makes the default NLS_DATE_FORMAT dd-mm-yy.
You can override this in the registry, or set up an after logon trigger.
Senior Oracle DBA