Oracle Database Discussions

Hi,

Oracle 10.2.0.3.0 on Windows 2003 R2 - Character set: UTF-8

Problem:

We have an application which is being upgraded to support multiple cultures (f.ex. American, German, Czech, Russian etc.).

There are some SQL's which get constructed at run time based on inputs from the application. We are trying to support data entry in user's own culture setting.

So an American user would input date in 'MM/DD/YYYY' format whereas a German user would input date in 'DD.MM.YYYY'. The column under consideration is a DATE and not a TIMESTAMP.

Accordingly, the DML is constructed as

INSERT INTO... VALUES (..., TO_DATE (:p1, System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern));

System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern - This is a .NET framework API for extracting the short date format based on current regional settings.

Thus, above SQL at runtime, would get translated as (f.ex.)

INSERT INTO... VALUES (..., TO_DATE (:p1, *'DD.MM.YYYY'*));

The problem comes with Czech and other cultures where the date format is d.M.yyyy - which is not supported by Oracle.

Oracle advises use of 'DS TS' format which is date short and time short formats - which works in above scenario.

But this does not seem to work is the end user (client) customizes his regional setting. Czech default 'd.M.yyyy' can be changed to 'mm.dd.yyyy' via regional settings in Windows and then Oracle DS TS format does not respect it.

Requirement:

I would like to know from the community if you have been in this situation before and how you have handled the same.

Thanks in advance,

Satish