Our java class calls PLSQL proc which returns date in default format which is defined by NLS_DATE_FORMAT. Our application sets its own Locale for internationalization but I want the date format to remain just 'DD-MON-RR' which is en_US Locale NLS_DATE_FORMAT. Due to the change in locale oracle's fetched Date string differs and subsequent TO_DATE() function calls are failing. I tried fixing this by changing Locale to Locale.setDefault(new Locale("en","US")); //"en_US" in java class and it works fine, but the internationalization part wont work anymore. I am in Singapore so my Locale is "en_SG" and the date format that oracle assumes after setting NLS_TERRITORY: SINGAPORE is NLS_DATE_FORMAT:'DD/MM/RR'. I queries server for V$NLS_PARAMETERS and there the default date format is 'DD-MON-RR'. So my question is, can i set the NLS_DATE_FORMAT without affecting Locale settings of application. Or can I make jdbc driver to ignore NLS settings of client altogether?
views:
31answers:
2Yo can use to_char function to format the date as you want, also you can include the third parameter in to_char function to denote locale used:
select to_char(sysdate, 'DY-MM-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') from dual;
More info about date formats can be found in SQL Reference.
I am kind of finalizing on the i18n stuff for my application and my design is based on below assumptions)
1) Locale.setDefault(new Locale("en","US")); is bad as you are changing the default locale for the JVM and not just for your app. So it is better to pass the locale around the app on a per request basis (may be threadlocal)
2) Handle all i18n formatting/parsing at the application level. DB should only handle comparisions (optionally), sorting (optionally) and storage. So just set the NLS_CHARACTERSET, NLS_COMP, NLS_SORT and NLS_LENGTH_SEMANTICS. By the way comparision and sorting at the db level means creating locale specific indexes thus slowing down the inserts/updates