RE: Different date formats between thin and thick clients

sorry, I'm late to the party again! ;-(

>> | using oci 9.2 | using thin
>> NLS_DATE_FORMAT | Mon DD YYYY HH24:MI:SS | DD-MON-RR

This is fair enough, but are we trying to "fix the database" when we should be fixing the code?

Ok, I realise that fixing the code will cost a lot more that attempting to set a parameter or two on the database and hope that it works, however, I suspect that some or all of the application code relating to date handling is not using TO_DATE() (or TO_CHAR() calls as appropriate.

One thing I learned many years ago when writing Oracle apps was that you "never mess with dates" and always always use TO_DATE() and TO_CHAR() calls when translating plus, you always supply a format string too - to be sure that the value you are passing will be stored correctly.

We had customers all over Europe and things were fine - the default date format worked ok, as soon as we started trading in the USA, it went belly up due to the customers there setting a [strange!} date format of 'MM/DD/YYYYY ...' so our nice little expectation that dates would be just fine fell apart quite quickly.

My job was to find all date handling code and fix it! Because of this, I know the pain involved with my initial suggestion above, but it really needs to be done by the sound of things. You simply cannot rely on the fact that the customers will all have the same default date format, nor can you rely on the fact that that format will be compatible with your application.

Other than that, I don't suppose there's any way that the application can issue an ALTER SESSION SET NLS_DATE_FORMAT = 'something suitable' to get over the problem in the mean time?

Cheers,
Norm.

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Team (EA)
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

--
http://www.freelists.org/webpage/oracle-l