Dates month names language in Oracle?

I have a varchar field in my table which contains a date, formatted as: Dic 31 1999 12:00AM, which can generally be converted without any problems using a to_date configuring 'Mon DD YYYY HH:MIAM'.

Month names can of course come in any language. For instance, January, would be Jan in English and Ene in Spanish.

I reckon the Oracle client will pick up the machine's locale to figure which is开发者_JS百科 the language of the incoming string. The only problem is that this will not always work if you are a server where you can't really know where the date is coming from.

My current problem is that every time I'm in a Spanish set computer and try to process a date with English named months, I'll get an "invalid month" error.

Is there I way I could tell Oracle, on execution time, which is the language I'm sending?

The current ugly workaround is to translate not matching months to Spanish.

You should use NLS_DATE_LANGUAGE for that:

select to_char(sysdate, 'dd Month, Day', 'NLS_DATE_LANGUAGE = turkish') from dual

You can use SELECT * FROM NLS_SESSION_PARAMETERS to determine you current language settings (also NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS exist).

Use NLS_* environment variables on your workstation or ALTER SESSION statements within your SQL session to change these language settings.

ALTER SESSION might work. I don't remember exactly how, but I know you can set locales, date styles and number formats with it.